Count unique value with multiple criteria in a range

[ExcelLearner_]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Customer Name
[/TD]
[TD]Customer Add
[/TD]
[/TR]
[TR]
[TD]ABC
[/TD]
[TD]Add 1
[/TD]
[/TR]
[TR]
[TD]ABC
[/TD]
[TD]Add 2
[/TD]
[/TR]
[TR]
[TD]CDE
[/TD]
[TD]Add 3
[/TD]
[/TR]
[TR]
[TD]BFE ( CDE Finance)
[/TD]
[TD]Add 3
[/TD]
[/TR]
</tbody>[/TABLE]

Hi guys,

I have a set of data consist of the below:
- Customer Name
- Customer Address
- Status
- Owner
- Type

I would like to count the number of customer with criteria
1. Status = Finalized
2. Owner = Bob
3. Type = Visit


The challenge as describe below (see table attached above)
1. one customer may have more than one address
2. one address may have more than one customer name.

Given all the criteria match, the total number of customer should be 2. (CDE & BFE are same entity with different subsidiary/department)

Tried using sum if together with 1/countifs formula - however it return 3...

Anyone can help shed some lights??

Many thanks in advance.

 

[Aladin Akyurek]

Given A1:B5, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(IF(FREQUENCY(IF($B$2:$B$5<>"",MATCH($B$2:$B$5,$B$2:$B$5,0)),ROW($B$2:$B$5)-ROW($B$2)+1),$A$2:$A$5,"")<>"",MATCH(IF(FREQUENCY(IF($B$2:$B$5<>"",MATCH($B$2:$B$5,$B$2:$B$5,0)),ROW($B$2:$B$5)-ROW($B$2)+1),$A$2:$A$5,""),IF(FREQUENCY(IF($B$2:$B$5<>"",MATCH($B$2:$B$5,$B$2:$B$5,0)),ROW($B$2:$B$5)-ROW($B$2)+1),$A$2:$A$5,""),0)),ROW($A$2:$A$5)-ROW($A$2)+1),1))

 

[ExcelLearner_]

It works !!

But can you enlighten me by explaining the logic please...?
Trying to wrap my mind around but could not get the concept ...

 

[ExcelLearner_]

Hi,
I manage to figure out how the formula works .. but how do I incorporate the criteria into the formula?

 

[ExcelLearner_]

Customer Name	Customer Add	Owner	Status	Type
ABC	Add 1	Bob	Finalized	Visit
CCD	Add 4	Steve	Finalized	Visit
EEF	Add 5	Bob	Open	Visit
FGH	Add 6	Bob	Finalized	Call
ABC	Add 2	Bob	Finalized	Visit
CDE	Add 3	Bob	Finalized	Visit
BFE	Add 3	Bob	Finalized	Visit

Hi,
I manage to figure out how the formula works .. but how do I incorporate the criteria into the formula?

sorry the screen shot seems missing when I post...
Table as illustrated above ...

 

[Aladin Akyurek]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer Name
[/TD]
[TD]Customer Add
[/TD]
[TD]Owner
[/TD]
[TD]Status
[/TD]
[TD]Type
[/TD]
[/TR]
[TR]
[TD]ABC
[/TD]
[TD]Add 1
[/TD]
[TD]Bob
[/TD]
[TD]Finalized
[/TD]
[TD]Visit
[/TD]
[/TR]
[TR]
[TD]CCD
[/TD]
[TD]Add 4
[/TD]
[TD]Steve
[/TD]
[TD]Finalized
[/TD]
[TD]Visit
[/TD]
[/TR]
[TR]
[TD]EEF
[/TD]
[TD]Add 5
[/TD]
[TD]Bob
[/TD]
[TD]Open
[/TD]
[TD]Visit
[/TD]
[/TR]
[TR]
[TD]FGH
[/TD]
[TD]Add 6
[/TD]
[TD]Bob
[/TD]
[TD]Finalized
[/TD]
[TD]Call
[/TD]
[/TR]
[TR]
[TD]ABC
[/TD]
[TD]Add 2
[/TD]
[TD]Bob
[/TD]
[TD]Finalized
[/TD]
[TD]Visit
[/TD]
[/TR]
[TR]
[TD]CDE
[/TD]
[TD]Add 3
[/TD]
[TD]Bob
[/TD]
[TD]Finalized
[/TD]
[TD]Visit
[/TD]
[/TR]
[TR]
[TD]BFE
[/TD]
[TD]Add 3
[/TD]
[TD]Bob
[/TD]
[TD]Finalized
[/TD]
[TD]Visit
[/TD]
[/TR]
</tbody>[/TABLE]



sorry the screen shot seems missing when I post...
Table as illustrated above ...

What are the additional criteria? How many - 3 as in Owner = Bob, Status = Finalized, and Type = Visit?

 

[ExcelLearner_]

Ya, I want to count the number of customer only if
1. Owner = Bpb and
2. Status = Finalized and
3. Type = Visit

 

[Aladin Akyurek]

Ya, I want to count the number of customer only if
1. Owner = Bpb and
2. Status = Finalized and
3. Type = Visit

Book1
	A	B	C	D	E	F	G
1	Customer Name	Customer Add	Owner	Status	Type		bob
2	ABC	Add 1	Bob	Finalized	Visit		finalized
3	CCD	Add 4	Steve	Finalized	Visit		visit
4	EEF	Add 5	Bob	Open	Visit		2
5	FGH	Add 6	Bob	Finalized	Call
6	ABC	Add 2	Bob	Finalized	Visit
7	CDE	Add 3	Bob	Finalized	Visit
8	BFE	Add 3	Bob	Finalized	Visit
Sheet2

In G4 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(IF(FREQUENCY(IF(($B$2:$B$8<>"")*($C$2:$C$8=G$1)*($D$2:$D$8=G$2)*($E$2:$E$8=G$3),MATCH($B$2:$B$8,$B$2:$B$8,0)),ROW($B$2:$B$8)-ROW($B$2)+1),$A$2:$A$8,"")<>"",MATCH(IF(FREQUENCY(IF(($B$2:$B$8<>"")*($C$2:$C$8=G$1)*($D$2:$D$8=G$2)*($E$2:$E$8=G$3),MATCH($B$2:$B$8,$B$2:$B$8,0)),ROW($B$2:$B$8)-ROW($B$2)+1),$A$2:$A$8,""),IF(FREQUENCY(IF(($B$2:$B$8<>"")*($C$2:$C$8=G$1)*($D$2:$D$8=G$2)*($E$2:$E$8=G$3),MATCH($B$2:$B$8,$B$2:$B$8,0)),ROW($B$2:$B$8)-ROW($B$2)+1),$A$2:$A$8,""),0)),ROW($A$2:$A$8)-ROW($A$2)+1),1))

What follows are more manageable versions...

Let's assign the ranges in the data area relevant names: cusname, cusadd, owner, status, and type. And let's also define ivec (from integer vector) via Formulas | Name Manager as referring to:

=ROW(cusname)-ROW(INDEX(Cusname,1,1))+1

Using these definitions the formula we have becomes more readable: Control+shift+enter…

=SUM(IF(FREQUENCY(IF(IF(FREQUENCY(IF((cusadd<>"")*(owner=G$1)*(status=G$2)*(type=G$3),MATCH(cusadd,cusadd,0)),ivec),cusname,"")<>"",MATCH(IF(FREQUENCY(IF((cusadd<>"")*(owner=G$1)*(status=G$2)*(type=G$3),MATCH(cusadd,cusadd,0)),ivec),cusname,""),IF(FREQUENCY(IF((cusadd<>"")*(owner=G$1)*(status=G$2)*(type=G$3),MATCH(cusadd,cusadd,0)),ivec),cusname,""),0)),ivec),1))

If so desired, we can make the formula a bit more efficient by using a non-native function written in VBA. The formula then becomes:

=SUM(IF(FREQUENCY(IF(V(IF(FREQUENCY(IF((cusadd<>"")*(owner=G$1)*(status=G$2)*(type=G$3),MATCH(cusadd,cusadd,0)),ivec),cusname,""))<>"",MATCH(V(),V(),0)),ivec),1))

For the foregoing formula to work, we need to install the V() function using Alt+F11 of which the code is as follows:

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function