Count Unique Values that Meet Criteria AT LEAST TWICE

Hi, been lurking for a while, first post

So I picked up this formula (and modded it a bit) from this forum to count the number of unique values that meet certain criteria:

=SUM(IF(FREQUENCY(IF($B$2418:$B$9151=$I$2422,IF($A$2418:$A$9151>=$I$2419,IF($A$2418:$A$9151<=$I$2420,MATCH("~"&$C$2418:$C$9151&"",$C$2418:$C$9151&"",)))),ROW($B$2418:$B$9151)-ROW($B$2418)+1),1))

Entered as an array. Basically the first three IF statements within the FREQUENCY command are testing for various criteria in columns A and B that the data in column C needs to meet to be counted and then the unique values in C that meet those criteria. My next step is to add a fourth criteria, but this criteria needs to be met AT LEAST TWICE to count the data in C as a unique value. Example:

A 1 hello fail
A 2 hello fail
B 1 hi pass
A 1 hello fail

So if I queried number of words that are A1 that failed at least twice, it should return 1, because hello was the only word that matched that criteria. Like I stated, the A1 criteria matching is not an issue and is covered by my above formula, but I can't figure out how to query unique values that match a criteria at least twice. Can anyone help? (If this approach is inherently wrong for this necessity, I am certainly open to abandoning this sum frequency approach as long as the new one can still perform the functions necessary) Thanks in advance!

No but in that formula there isn't the column (column E) that requires the 2 or more criteria. Which criteria did you match it to in yours?

Edit: So if I understand it right, does your modification check for each parameter to have at least 2 occurrences before it returns a positive?

Yes, but only one of the parameters needs to be matched twice, the rest of the parameters only need to match once. Your modification is very close to what I need, could you maybe tell me how to separate that >=2 to only apply to one of my IF statements? Such as this:

=SUM(IF(FREQUENCY(IF($B$2418:$B$9151=$I$2422,IF($A$2418:$A$9151>=$I$2419,IF($A$2418:$A$9151<=$I$2420,IF($E$2418:E$9151="1FAIL_WH",MATCH("~"&$C$2418:$C$9151&"",$C$2418:$C$9151&"",))))),ROW($B$2418:$B$9151)-ROW($B$2418)+1),1))

Only the bolded parameter has to be repeated twice, the rest of the parameters only need to be checked once (as is).

Thanks so much in advance!

Okay I tested that and did some troubleshooting, it looks like that outside IF statement is checking the entire column (not just within the date range and type selected) and if it passes (meaning more than two products returned 1FAIL_WH since 2010), then it just counts normally, meaning produces the same result as without the IF. So I just need to apply that condition within the boundaries set by the user (meaning the date and type).

I tried repeating my inside boundary conditions on the outside of that IF so it looks like this:

=IF($B$2418:$B$9151=$I$2422,IF($A$2418:$A$9151>=$I$2419,IF($A$2418:$A$9151<=$I$2420,IF(COUNTIF($E$2418:E$9151,"1FAIL_WH")>=2,SUM(IF(FREQUENCY(IF($B$2418:$B$9151=$I$2422,IF($A$2418:$A$9151>=$I$2419,IF($A$2418:$A$9151<=$I$2420,MATCH("~"&$C$2418:$C$9151&"",$C$2418:$C$9151&"",)))),ROW($B$2418:$B$9151)-ROW($B$2418)+1),1)),""))))

Where the bolded parts are repeated, but I get an error of nesting too many functions. When I tried removing the inside boundary conditions (and moved them all to outside the sum function), I always get returned a FALSE value. Do those IF statements have to be within the sum function?

I tried your code (and I moved it to xlsx which accepted that many nested statements) and both returned the same result, which is still ignoring that fourth parameter and counting it how it has been counted. I'm not sure but it looks like all the parameters have to be within the sum statement because otherwise it treats it as a "check" of sorts where it will say I can count if I meet this parameter, but when it counts, it still counts the same way whereas I will always allow it to count but when it does count, I need it to count differently than it already is. Am I right in saying that adding that parameter that has to be counted twice has to be within the sum statement?

example.xls

Okay so here, if I were to query HJF-IT-4E in this time period, I would like to see how many batches were produced (I've done that with the formula in OP) as well as how many batches were deemed to fail. I've highlighted in red an example of a batch that failed (>= 2 products from that batch failed) and a batch that passed in green (<2 products from that batch failed). Thanks again for all your help!

Your first batch (S7M8AQU0) is listed twice, top and bottom; your second batch (ST5ABU07) had one product fail, but the batch itself did NOT fail because when the second product from that batch was tested, the second product passed (only batches with >= 2 products that fail are failed); same situation with your 4th batch (S7N6AHU0), one product failed, one product passed, so the batch passed.

WORKED PERFECT :D

Thank you and NBVC so much, I've been working at this for the past two days and couldn't get it! I've tested this extensively and it works exactly as needed! Just one last, quick question: is there anything in that array formula that would malfunction if rows were hidden? Or any restrictions about the arrangement of cells (other than the obvious cell references)? Thanks again!!

example2.xls

I have one last, last, (hopefully) quick question: As it turns out, there are two product type names that actually refer to the same product (HJF-IT-4E and HJF-QU-04E) and are essentially interchangeable in terms of one batch can have several products with one product type and several with the other product type. Since Haseeb's formula only captures batches that are exclusively one of the product types, I was getting unnaturally low failure rates. I've tried a modification:

=SUM(IF(FREQUENCY(IF(OR($B$2:$B$4519="HJF-IT-4E",$B$2:$B$4519="HJF-QU-04E"),IF($E$2:$E$4519="FAIL",MATCH($C$2:$C$4519&"|FAIL",$C$2:$C$4519&"|"&$E$2:$E$4519,0))),ROW($B$2:$B$4519)-ROW($B$2)+1)>=2,1))

By adding the OR() modifier, the equation functions somewhat oddly. What essentially happens is that after the OR() modification it completely ignores the product type parameter and returns all batches that failed. As it stands, it DOES return the correct answer (5) BUT it does so because of a fluke. For example, this formula also returns 5:

=SUM(IF(FREQUENCY(IF(OR($B$2:$B$4519="HJF-IT-4E",$B$2:$B$4519="WTFWHY"),IF($E$2:$E$4519="FAIL",MATCH($C$2:$C$4519&"|FAIL",$C$2:$C$4519&"|"&$E$2:$E$4519,0))),ROW($B$2:$B$4519)-ROW($B$2)+1)>=2,1))

As you can see, it is ignoring the text in the quotations entirely and simply returning the number of batches that failed, regardless of product type. Can anyone shed any light on why this is and how I can fix it?

Originally Posted by Haseeb A

You can't use OR here. If you only have just two 'Type' use them between {} in IF,

=SUM(IF(FREQUENCY(IF($B$2:$B$4519={"HJF-IT-4E","WTFWHY"},IF($E$2:$E$4519="FAIL",MATCH($C$2:$C$4519&"|FAIL",$C$2:$C$4519&"|"&$E$2:$E$4519,0))),ROW($B$2:$B$4519)-ROW($B$2)+1)>=2,1))

Or,

enter all possible Types in a range, ie K2:K20, then use

=SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH($B$2:$B$4519,$K$2:$K$20,0)),IF($E$2:$E$4519="FAIL",MATCH($C$2:$C$4519&"|FAIL",$C$2:$C$4519&"|"&$E$2:$E$4519,0))),ROW($B$2:$B$4519)-ROW($B$2)+1)>=2,1))

Ahh you've outdone yourself again, it works perfectly!! I can't say thank you enough!!