CODEWITHSUNDEEP
excelexcel-formula
HotSauceCoconuts
HotSauceCoconuts

Reputation: 321

Check all values in range match criteria based on string index criteria

I need to check that all instances of the value in the blue table Index column have a corresponding value of YES, and report this data into the green table.

enter image description here

My attempted solution
I think I have a solution... but surely there is something better than this janky method!

={SUM((Table1[INDEX]=[@INDEX])*(Table1[VALUE]="YES"))=COUNTIF(Table1[INDEX],Table2[@])}

Upvotes: 1

Views: 87

Answers (2)

VBA Pete
VBA Pete

Reputation: 2666

You could also just count "No" rather than "Yes", which simplifies the formula:

=IF(COUNTIFS(A:A,D1,B:B,"NO")>0,"NO","YES")

Upvotes: 2

ABC
ABC

Reputation: 66

Try this formula under your "PASS" column in green table:

=IF((COUNTIF(A:A,D2))=COUNTIFS(B:B,"YES",A:A,D2),"YES","NO")

Note:
Change the column value regarding your column (based on your excel). The above formula is written for your table considering your BLUE table is Column A, B -- empty column C -- GREEN table is Column D, E

Upvotes: 3

Related Questions