Counting distinct values based on two criteria one of which should be a range

[youreskimofriend]

Hey everybody,

I already managed to almost solve my problem with putting together a rather long formula from existing threads but when it comes to those ranges I give up.

I have a very large data set (30k rows, no blanks) and I am trying to count distinct account numbers based on two criteria but one should capture a range.

This is a simplified version:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Account Nr.[/TD]
[TD]Country[/TD]
[TD]Age of purchase (years)[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]USA[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]USA[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]234[/TD]
[TD]USA[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]234[/TD]
[TD]USA[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]345[/TD]
[TD]Germany[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]











The question I am trying to answer is: How many clients from the USA have purchased something 1-3 years or 3-5 years ago. How many clients from Germany, etc.

I was able to figure out the formula for each individual year, but not the ranges. E.g. for year 2 I can get the distinct US clients like this:

=SUM((IF((C2:C6=2)*(B2:B6="USA")*(A2:A6<>"");1/COUNTIFS(A2:A6;A2:A6;C2:C6;2;B2:B6;"USA";A2:A6;"<>"))))

Any help is greatly appreciated!

Andi

 

[Comfy]

Something like this might help:

Excel 2010

	A	B	C
1-3	3-5
USA
Germany

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Account Nr.[/TD]
[TD="bgcolor: #FAFAFA"]Country[/TD]
[TD="bgcolor: #FAFAFA"]Age of purchase (years)[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]123[/TD]
[TD="bgcolor: #FAFAFA"]USA[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]123[/TD]
[TD="bgcolor: #FAFAFA"]USA[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]234[/TD]
[TD="bgcolor: #FAFAFA"]USA[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]234[/TD]
[TD="bgcolor: #FAFAFA"]USA[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]345[/TD]
[TD="bgcolor: #FAFAFA"]Germany[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B14[/TH]
[TD="align: left"]=COUNTIFS($B$2:$B$6,$A14,$C$2:$C$6,"<=3")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C14[/TH]
[TD="align: left"]=COUNTIFS($B$2:$B$6,$A14,$C$2:$C$6,"<=5")-COUNTIFS($B$2:$B$6,$A14,$C$2:$C$6,"<=2")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B15[/TH]
[TD="align: left"]=COUNTIFS($B$2:$B$6,$A15,$C$2:$C$6,"<=3")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C15[/TH]
[TD="align: left"]=COUNTIFS($B$2:$B$6,$A15,$C$2:$C$6,"<=5")-COUNTIFS($B$2:$B$6,$A15,$C$2:$C$6,"<=2")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Your criteria will result in purchases of 3 years being counted twice.

 

[youreskimofriend]

Thanks Comfy!

However, if I understand your solution correctly, it would only count purchases but not unique clients. I meant to count the number of distinct account numbers based on the two criteria, e.g. for the range 1-5 years, there are 2 US clients (123 and 234). Is there any way to do this with your solution?

Thanks!

 

[Comfy]

Ok, so I've edited the formula which has been posted here: Formula to count unique values with criteria « Chandoo.org - Learn Excel & Charting Online - Forums

It may not be the most elegant way to do this but it seems to work.

Don't ask me how it works because I don't know, I only know enough to edit it :DD

Excel 2010

	A	B	C	D	E	F	G	H
1-3	3-5
USA
Germany

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Account Nr.[/TD]
[TD="bgcolor: #FAFAFA"]Country[/TD]
[TD="bgcolor: #FAFAFA"]Age of purchase (years)[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]123[/TD]
[TD="bgcolor: #FAFAFA"]USA[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]123[/TD]
[TD="bgcolor: #FAFAFA"]USA[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]234[/TD]
[TD="bgcolor: #FAFAFA"]USA[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]234[/TD]
[TD="bgcolor: #FAFAFA"]USA[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]345[/TD]
[TD="bgcolor: #FAFAFA"]Germany[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G3[/TH]
[TD="align: left"]{=SUM(IF(FREQUENCY(IF($B$2:$B$6=$F3,IF($C$2:$C$6<4,MATCH($A$2:$A$6,$A$2:$A$6,0))),ROW($A$2:$C$6)-ROW($B$2)+1),1))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H3[/TH]
[TD="align: left"]{=SUM(IF(FREQUENCY(IF($B$2:$B$6=$F3,IF($C$2:$C$6<6,IF($C$2:$C$6>2,MATCH($A$2:$A$6,$A$2:$A$6,0)))),ROW($A$2:$C$6)-ROW($B$2)+1),1))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[youreskimofriend]

FAN-TAS-TIC! I have no idea why but it works perfectly!

Thanks so much!