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:

  1. Click Start.
  2. Type cmd, and then press CTRL+SHIFT+ENTER to open the elevated command prompt (I.e. Command prompt with administrative rights).
  3. The User Account Control window should appear, be sure that the action it exhibits is correct, and then press the Continue button.
  4. Type or copy paste the below command line into it without quotes:
  5. "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.

2. After the scan is done, you can see the errors and problems which need to be repaired.

3. When the Fixing part is finished, your computer has been speeded up and the errors have been fixed


Related: How Can You Update & Download NVidia 6100, 6800 GS/XT Display Driver v.260.89 WHQL,Where to Download NVidia GeForce 8800 GT WHQL-certified driver v.196.21,How to Update & Download NVidia GeForce Go 7400 Display Driver v.260.19.12,[Solved] Download NVidia GeForce GTX 460M Driver v.331.82,Download NVidia Quadro Plex Model IV VGA Driver v.304.43 Certified,Way to Download RealTek RTL8100C(L) Driver v.5.01,Way to Download RealTek RTL8100E Drivers v.694,Way to Update & Download RealTek RTL8101L Auto Installation Program v.6.110 driver,Method to Update & Download RealTek RTL8111G PXE and RPL ROM code v.2.58 driver,How to Update & Download RealTek RTL8411B(N) Driver v.10.003,Best Way to Update & Download ASUS A53SV nVidia Graphics Driver v.8.17.12.6686,Method to Herunterladen ASUS K75VJ Intel Rapid Storage Technology Treiber v.11.6.0.1030,How Can You Update & Download ASUS CG8580 Intel Chipset Driver v.9.3.0.1019,Method to Update & Download ASUS K41VD Intel INF Update Driver v.9.1.1.1015,Way to Update & Download ASUS Pro70T NB Probe v.3.0.0026 driver
Read More: How to Fix Problem - Connection being dropped?,How Can I Fix - Counting rows below a row if it meets the criteria?,Troubleshooting:Corrupted drive notice for failure to backup listed files with 0X8100002F error Error,[Anwsered] Copying date data range between cells using mouse drag,How to Resolve - corel painter sketchpad?,Closing Internet Explorer causes error,Compare 2 Tables and Calculate the PROFIT Faster,Computer goes to Windows Boot Manager after Start,Connect to one of my network computer remotely when don't allow exception box Marked,computer locking up randomly

没有评论:

发表评论