Unique/conditional count formula

user_16149504 0
Viewed 53

I'm looking to calculate if there was an upgrade for a supplier, not the total count.

So, for each contract, for each supplier, was there an upgrade (regardless of the count/sum of upgrades)? If the result is yes, then the answer is 1.

enter image description here

In the example above the count of the upgrades is 9, but the answer I am looking to generate is 4.

I am guessing to calculate this I would need a combination of IF, AND, COUNTIF, MAX, but I can't seem to put it together.

Can anyone please help me out?

Thanks!

excel-formula excel
3 Answers

Try below formula-

=SUM(--(UNIQUE(FILTER(B2:B15&A2:A15,D2:D15>0))<>""))

enter image description here

user_55147470

Below formula should works. Reference here

=IFERROR(ROWS(UNIQUE(FILTER(B2:B15, D2:D15>0))), 0)
user_123284970

You could try the following:

enter image description here

Formula in G2:

=SUM((D2:D15>0)*(IFERROR(1/COUNTIFS(A2:A15,A2:A15,B2:B15,B2:B15,D2:D15,">0"),0)))

This is an CS-entered array formula if you are not working through MS365.

user_97581940
Related