If sum of duplicate values meets condition, count different values

[airjay]

Hello everyone,

Nice forum you've got here, I hope my first thread will be useful for others with similar problems.

I need to count how many different values are there in a column which meet a condition. I intended to use this formula:

           {=[URL="https://exceljet.net/excel-functions/excel-sum-function"]SUM[/URL](--([URL="https://exceljet.net/excel-functions/excel-frequency-function"]FREQUENCY[/URL]([URL="https://exceljet.net/excel-functions/excel-if-function"]IF[/URL](criteria,[URL="https://exceljet.net/excel-functions/excel-match-function"]MATCH[/URL](values,values,0)),[URL="https://exceljet.net/excel-functions/excel-row-function"]ROW[/URL](values)-[URL="https://exceljet.net/excel-functions/excel-row-function"]ROW[/URL](values.firstcell)+1)>0))}

It was working fine. The thing is, I have different purchase orders from a client. Among other conditions, an important one is to count how many purchase orders were for <25K€ , 25K€< P.O.<100K€, 100K€<P.O. It'd be as easy as introducing the condition IF(Price<25000; ... ), BUT some P.O account for different products, thus dividing their amount into different rows in my table. I might have a P.O for 50K€ divided into 4 rows for products costing 12.5K€ each. See example for more clarity.

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Price
[/TD]
[TD]P.O
[/TD]
[/TR]
[TR]
[TD]5000
[/TD]
[TD]A001
[/TD]
[/TR]
[TR]
[TD]10000
[/TD]
[TD]A001
[/TD]
[/TR]
[TR]
[TD]15000
[/TD]
[TD]A001
[/TD]
[/TR]
[TR]
[TD]30000
[/TD]
[TD]A002
[/TD]
[/TR]
[TR]
[TD]2000
[/TD]
[TD]A003
[/TD]
[/TR]
[TR]
[TD]4000
[/TD]
[TD]A004
[/TD]
[/TR]
</tbody>[/TABLE]

The expected result is 2 purchase order below 25K€ (A001 and A002) and 2 above 25K€ (A003 and A004)

I thought of something like IF(SUMIF([P.O];[P.O];[Price])<25000; ... ) , but it doesn't seem to work. I'm really struggling with this.

Regards.

 

[Aladin Akyurek]

There is no price < 25000 for A002... so what is the expected distinct count for < 25000? Looks like 1...

 

[airjay]

My bad. It was the opposite, A001 (5k + 10k + 15k ) and A002 are over 25k. A003 and A004 are below 25k.

 

[Aladin Akyurek]

My bad. It was the opposite, A001 (5k + 10k + 15k ) and A002 are over 25k. A003 and A004 are below 25k.

So, the count is 2, thas is, it consists of A003 and A004.

Unque count, control+shift+enter, not just enter...

1. when less than 25000...

=SUM(IF(FREQUENCY(IF(1-(B2:B7=""),IF(SUMIFS(A2:A7,B2:B7,B2:B7) < 25000,MATCH(B2:B7,B2:B7,0))),ROW(B2:B7)-ROW(B2)+1),1))

2. when between >= 25000 and < 100000...

=SUM(IF(FREQUENCY(IF(1-(B2:B7=""),IF(SUMIFS(A2:A7,B2:B7,B2:B7) >= 25000,IF(SUMIFS(A2:A7,B2:B7,B2:B7) < 100000,MATCH(B2:B7,B2:B7,0)))),ROW(B2:B7)-ROW(B2)+1),1))