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:

Account Nr.	Country	Age of purchase (years)
123	USA	1
123	USA	2
234	USA	2
234	USA	3
345	Germany	1

<tbody>
</tbody>

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	Account Nr.	Country	Age of purchase (years)
2	123	USA	1
3	123	USA	2
4	234	USA	5
5	234	USA	3
6	345	Germany	1
7
8
9
10
11
12
13		1-3	3-5
14	USA	3	2
15	Germany	1	0

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B14 =COUNTIFS($B$2:$B$6,$A14,$C$2:$C$6,"<=3") C14 =COUNTIFS($B$2:$B$6,$A14,$C$2:$C$6,"<=5")-COUNTIFS($B$2:$B$6,$A14,$C$2:$C$6,"<=2") B15 =COUNTIFS($B$2:$B$6,$A15,$C$2:$C$6,"<=3") C15 =COUNTIFS($B$2:$B$6,$A15,$C$2:$C$6,"<=5")-COUNTIFS($B$2:$B$6,$A15,$C$2:$C$6,"<=2") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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	Account Nr.	Country	Age of purchase (years)
2	123	USA	5				1-3	3-5
3	123	USA	1			USA	2	2
4	234	USA	5			Germany	0	1
5	234	USA	2
6	345	Germany	5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula G3 {=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))} H3 {=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))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[youreskimofriend]

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

Thanks so much!