How to Use the AVI Converter: Fast Solution to Error: Counting unique instances with other conditions

2016年9月23日星期五

Fast Solution to Error: Counting unique instances with other conditions

Counting unique instances with other conditions

I'm sure there is a way to do this, but it is late and my brain is to tired to figure it out.

User     Pass   Test
ABC      YES        1
ABC      NO          1
ABC      YES        2
DEF      YES        1
DEF      YES         2
GHI       NO          1

I'm building a report based on a much larger data set, and need to produce summary results.
I've built my report layout, and have the users in a data validation list (I only need to show one user at a time).

The one piece I'm having trouble with generating a count of the unique test IDs (3rd column) by user.
I can easily count that ABC shows up three times, and passed twice.
What I'm struggling with is the easiest way to show that ABC took a total of two different
tests (#1 and #2).
My other formulas are all countifs and sumifs, but I'm not sure how to build in the criteria to only count unique test numbers without altering or reformatting the source data.

Thanks!
Keith

Keys to the Problem Counting unique instances with other conditions

Download Error Fixer for Free Now

Someone else may be able to do this without helper columns, but I need a couple of extras to get it done, along with a little help from Advanced Filtering.
[EDIT] CharAbeuh has done exactly that in the next post!!
Start with a new column that we will label as Combined, for this example it is column E and in E2 put this formula:
=A2 & C2
which will result in ABC1 being displayed. Fill it down to the end of the list of user/pass/test entries.
Next we use Advanced Filter with the Unique Entries option to get a list of unique entries from our helper column. Select column E and then on the [Data] tab choose "Advanced..." from the Sort & Filter group.
Choose the "Copy to another location" option;
click in the "Copy to:" entry area and click cell F1 on the sheet, check the "Unique records only" box and click the [OK] button.
That will give you a list of unique entries from the helper column. It would have 5 entries in it based on your sample data:
ABC1
ABC2
DEF1
DEF2
GHI1
Now we're ready to actually determine the number of test attempts. So that we can just use 1 formula to test any user's attempts, we will use cell G1 for entries that match the User entries (ABC, DEF and GHI) and we will put the counting formula into H1.
Here is the formula for H1:
=SUMPRODUCT(--(LEFT(F:F,3)=G1)*1)
Note that if you don't have anything in G1, it will give you a large number (number of empty rows in column F). But when you enter ABC, DEF or GHI into it, it will give you a count of those types of entries from column F. You can change the formula to
only look at the used cells in column F, but then you have to change it as the number of entries in it changes as you continue to refresh the list using the Advanced filtering feature in later times.

Use the System File Checker tool to repair corrupted or missing system files:

Click Start.
Type cmd, and then press CTRL+SHIFT+ENTER to open the elevated command prompt (I.e. Command prompt with administrative rights).
The User Account Control window should appear, be sure that the action it exhibits is correct, and then press the Continue button.
Type or copy paste the below command line into it without quotes:
"Sfc /scannow"

Recommended Method to Fix the Problem: Counting unique instances with other conditions:

How to Fix Counting unique instances with other conditions with SmartPCFixer?

1. Download SmartPCFixer . Install it on your computer. Click Scan, and it will perform a scan for your computer. The errors will be shown in the scan result.