Count query

BrutalLogiC · Mar 26, 2014

Hi there

I want to count the number of purchase orders that total more than £1,000

Column A is the list of purchase orders
Column B is the description
Column C is the cost

The problem for me is for each purchase order there might be more than one line item, e.g.
PO999, description = 2 hours labour, cost = £800
PO999, description = 10 widgets, cost = £500

So overall PO 999 is more than £1,000 so I can't just count the number of values over £1,000 in column C as it is not clever enough to take into account multiple rows.

Help please with a formula that can handle this.

Hope that's clear!

Thanks

Marcelo Branco · Mar 26, 2014

Maybe something like this

	A	B	C	D	E
1	Order#	Description	Value		Count of Orders > 1000
2	1	desc1	600		3
3	2	desc2	1200
4	3	desc3	400
5	3	desc4	500
6	4	desc5	500
7	4	desc6	600
8	5	desc7	1000
9	6	desc8	300
10	6	desc9	500
11	6	desc10	400

Formula in E2
=SUMPRODUCT(--(SUMIF(A2:A11,A2:A11,C2:C11)>1000),--(MATCH(A2:A11,A2:A11,0)=ROW(A2:A11)-ROW(A2)+1))

Hope this helps

M.

BrutalLogiC · Mar 26, 2014

Thanks for that, works well

Marcelo Branco · Mar 26, 2014

BrutalLogiC said:
Thanks for that, works well

You are welcome and thanks for the feedback.

M.

BrutalLogiC · Apr 1, 2014

Hi again

I was wondering how I would change the formula so I could look at values between certain ranges, e.g. count the number between 800 and 999?

Cheers

Marcelo Branco · Apr 1, 2014

BrutalLogiC said:
Hi again

I was wondering how I would change the formula so I could look at values between certain ranges, e.g. count the number between 800 and 999?

Cheers

Could you provide a data sample and expected result(s)?

M.

BrutalLogiC · Apr 1, 2014

In the data that you kindly provided above I would like a formula that for example searched for all orders between 0 and 500 the answer would be 0 since they are all above £500. Or if I wanted orders between £1,001 and £1,150 the answer would be 2 (order numbers 3 and 4 which are both for £1,100).

Something like =SUMPRODUCT(--(SUMIF(A2:A11,A2:A11,C2:C11)>1001 & <1150),--(MATCH(A2:A11,A2:A11,0)=ROW(A2:A11)-ROW(A2)+1)) ??

Marcelo Branco · Apr 1, 2014

	A	B	C	D	E
1	Order#	Description	Value		1000 < Orders < 1150
2	1	desc1	600		2
3	2	desc2	1200
4	3	desc3	400
5	3	desc4	650
6	4	desc5	500
7	4	desc6	600
8	5	desc7	1000
9	6	desc8	300
10	6	desc9	500
11	6	desc10	400

Formula in E2
=SUMPRODUCT(--(SUMIF($A$2:$A$11,$A$2:$A$11,$C$2:$C$11)>1000),--(SUMIF($A$2:$A$11,$A$2:$A$11,$C$2:$C$11)<1150),--(MATCH(A2:A11,A2:A11,0)=ROW(A2:A11)-ROW(A2)+1))

M.

BrutalLogiC · Apr 1, 2014

Thank you that works! Takes a long time to calculate on my pc I guess because it's slow and the range is over 1000 rows so quite big? The values take awhile to actually update.

If you change your formula so it is looking at rows 2 to 15 then I get a #N/A answer, is that right? The range can't include rows with no data in them?

EDIT: I have a further column with building names. Is there a way that I can get your formula to update when I filter by a particular building? Like a =subtotal(9, function in front? Hope that's clear.

Marcelo Branco · Apr 1, 2014

BrutalLogiC said:
EDIT: I have a further column with building names. Is there a way that I can get your formula to update when I filter by a particular building? Like a =subtotal(9, function in front? Hope that's clear.

This is a different problem. I suggest you create a new thread.

M.

Count query

BrutalLogiC

Active Member

Excel Facts

Marcelo Branco

MrExcel MVP

BrutalLogiC

Active Member

Marcelo Branco

MrExcel MVP

BrutalLogiC

Active Member

Marcelo Branco

MrExcel MVP

BrutalLogiC

Active Member

Marcelo Branco

MrExcel MVP

BrutalLogiC

Active Member

Marcelo Branco

MrExcel MVP

Similar threads

Forum statistics

Share this page

Count query

Active Member

Excel Facts

MrExcel MVP

Active Member

MrExcel MVP

Active Member

MrExcel MVP

Active Member

MrExcel MVP

Active Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock