CODEWITHSUNDEEP
excelexcel-formulaformulavba
Mowgli
Mowgli

Reputation: 3512

How to only count unique record once and based on that count Yes or No

I have 3 columns

ORDER, ID and E Yes/NO

In Column C order In Column D ID In Column E Yes/No

For example for ID = 144. I need count to how many order it was given to 144. so looking at sample table below 144 was given 1 order which was 821 and column is Yes for 144 and when Order = 821.

Another example ID=162. was given 2 order , 861 and 992. so his total order count is 2 and # of Yes count is 1 because row 13 is No.

I am really stuck on this complex logic. Any feedback would be appreciated.

enter image description here

Upvotes: 0

Views: 680

Answers (1)

lori_m
lori_m

Reputation: 5567

Given the layout shown, try these in B17 and C17 respectively, then fill down:

=SUM(IF(FREQUENCY(IF(MMULT(-(D$2:D$13=A17),1),C$2:C$13),C$2:C$13),1))
=SUM(IF(FREQUENCY(IF(MMULT((D$2:D$13=A17)*(E$2:E$13="YES"),1),C$2:C$13),C$2:C$13),1))

Upvotes: 1

Related Questions