CODEWITHSUNDEEP
excelcountcountifsumifs
sean
sean

Reputation: 5

Using Countifs to count distinct values and multiple criteria?

I am trying to count how many brands a certain client has passed through. I essentially have two columns a client list and a brand column.

In some cases, there may be duplicates of the brand so I only need to count them once.

I have tried to use several variants Countifs/Sumifs formulas but to no success.

Totally lost and would appreciate any help Example

Thanks

Upvotes: 0

Views: 3997

Answers (2)

Gravitate
Gravitate

Reputation: 3064

Here is a formula which does not require any helper column.

=SUMPRODUCT((($A$2:$A$13=$E2)*($B$2:$B$13<>""))/COUNTIF($B$2:$B$13,$B$2:$B$13&""))

The ($A$2:$A$13=$E2) checks that the name matches.

The ($B$2:$B$13<>"") just makes sure that any blanks are ignored (not counted as a brand).

The COUNTIF($B$2:$B$13,$B$2:$B$13&"") looks at unique brands only.

enter image description here

Upvotes: 1

n8-da-gr8
n8-da-gr8

Reputation: 551

Try: =SUMPRODUCT(--(COUNTIFS($A:$A,$D2,$B:$B,$H$1:$H$10)>0))

enter image description here

You'll need to make a helper column which contains all the brands (no duplicates). The only thing that should change in the formula is $D2, which is the Client in question. So it will become $D3, $D4, etc.

Upvotes: 1

Related Questions