1

I'm working on a project that has many columns and a few sheets. For simplicity, I'll post just the ones I'm in need of help for: In a table, I have a username and whether or not they received a certificate. The calculation of the certificate is based on the number of passes they have on other tests (three or more gives them a certificate). On another sheet, I'm posting statistics where I would like to show just the number of unique certificates total. The table looks something like below (simplified).

*Username* | *Test Result* | *Bundle*
abc        | Pass          | ""
abc        | Pass          | ""  
123        | Pass          | True
123        | Pass          | True
123        | Pass          | True
123        | Pass          | True
qwerty     | Fail          | False
qwerty     | Pass          | False
qwerty     | Pass          | False

So with the above, they need three or more tests to have the "Bundle". If they pass two and fail the third they get a fail for the "Bundle". If they haven't taken three tests, it doesn't count as a pass or fail.

On another sheet, I want to show the number of unique students whom have gotten the "Bundle" and whom haven't.

Some of the things I've tried:

=COUNTIFS(MOS_Table[Username],"*",MOS_Table[Bundle],TRUE)

(NOTE: MOS_Table is the name of the table) This will count the total number of TRUE values in the Bundle column but counts the same student for each test they have taken. 

=COUNTIF(MOS_Table[Bundle],TRUE)

This returned the same as above.

I've tried others with SUMPRODUCT and combinations of SUM, COUNTIFS, and other things. I'm not sure if there's a better way to go about this, but I'll take any hack to get it to work. Long term, there will be up to 400-500 possible rows.

Thanks in advance!

Dekaiden
  • 45
  • 8
  • Have you tried a pivot table with a simple count filter of 1 then just have a lookup to what you need? Its a bit of a workaround but would seem to achieve your goal. – Hatt Apr 29 '16 at 16:10
  • I haven't gotten into Pivot Tables, so I'm not sure of their use or capabilities. I do need the statistics sheet to have a few tables on it with details of each test and their pass rates, among other data. Does this sound like something Pivot Tables could do for me? – Dekaiden Apr 29 '16 at 16:35

1 Answers1

0

Please try this:

=SUM(IF(MOS_Table[Bundle]=TRUE,1/COUNTIF(MOS_Table[Username],MOS_Table[Username])))

This is an array-formula and must be confirmed with ctrl+shift+enter!

The countif returns an array of the full counts {2,2,4,4,4,4,3,3,3} and the 1/ makes it so it can be summed up, but only for the true part of the IF {FALSE,FALSE,0.25,0.25,0.25,0.25,FALSE,FALSE,FALSE}. Which is 1 in your example.

If you still have any questions, just ask :)

Dirk Reichel
  • 7,989
  • 1
  • 15
  • 31
  • Just as a note: this will mess up if not ALL of the `*Bundle*` are `TRUE` (or none of them)... Keep that in mind :) – Dirk Reichel Apr 29 '16 at 16:58
  • I cannot thank you enough for this! I've been working on it for a couple days trying to figure it out. I came close to something like this but had the Bundle and Username columns backwards and didn't do the 1/ part. This is awesome. The `*Bundle*` column doesn't have all TRUE and there are blanks, however, this still seems to work just fine. I've been running a few tests and everything is working great. Thank you again! – Dekaiden Apr 29 '16 at 17:51