Count query

[BrutalLogiC]

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

 

[BrutalLogiC]

Will do.

 

[BrutalLogiC]

Hi,

Would I be able to show the value of orders within the given range rather than display the number of orders between a given range?

Somehow modify the =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)) formula??

In the very first example where it shows 3 orders over £1,000, I would like to modify this to show the total value of those 3 orders. Not sure if this is complex and needs a new thread!

 

[Aladin Akyurek]

Is there a compelling reason why you don't create a total range and inquire that range faor the queries you have?

Order#	Description	Value	Total
1	desc1	600	600
2	desc2	1200	1200
3	desc3	400	1050
3	desc4	650
4	desc5	500	1100
4	desc6	600
5	desc7	1000	1000
6	desc8	300	1200
6	desc9	500
6	desc10	400

<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3726" width=105><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2503" width=70><TBODY>
</TBODY>

D2, copied down:

=IF(ISNA(VLOOKUP(A2,$A1:$D1,4,0)),SUMIF($A$2:$A$11,$A2,$C$2:$C$11),"")

 

[BrutalLogiC]

Thanks for reply. If there is an easier way of doing it I'm all for that, not sure what you mean by creating a total range and inquire that range, is this some sort of standard function?

 

[Aladin Akyurek]

Thanks for reply. If there is an easier way of doing it I'm all for that, not sure what you mean by creating a total range and inquire that range, is this some sort of standard function?

http://www.mrexcel.com/forum/excel-questions/766939-count-query.html#post3767915

tells you how to create the total range.:

The data is in A:C. In column D, you enter the formula that is provided.

Counting values > 1000 becomes...

=COUNTIF(D2:D11,">1000")

for example.

 

[BrutalLogiC]

I think the problem with this is each purchase order may have more than one line item and this won't be clever enough to take that into account?
if A1 is order number 111 and is for £500 and A2 is also order number 111 and is for £750
Then order 111 is over £1,000 but I can't just count the rows over £1,000 as it would miss order 111 out since neither of the individual line items are over £1,000

 

[Aladin Akyurek]

I think the problem with this is each purchase order may have more than one line item and this won't be clever enough to take that into account?
if A1 is order number 111 and is for £500 and A2 is also order number 111 and is for £750
Then order 111 is over £1,000 but I can't just count the rows over £1,000 as it would miss order 111 out since neither of the individual line items are over £1,000

Apparently I'm misunderstanding and bloating the thread unwillingly... I apologize. Just for the record, given the data in A1:C11, the headers included:

Order#		Value
1		600
2		1200
3		400
3		650
4		500
4		600
5		1000
6		300
6		500
6		400

<TBODY>
</TBODY>

What are your queries along with the actual results thet yo want to see?

 

[BrutalLogiC]

Please look at this screen shot
http://i.imgur.com/vi0aDAv.png
In cell F6 I want to calculate the total £cost of the 6 orders between £1001 and £1500

In the above example there are 6 orders. Some of the orders have multiple line items, e.g. orders 3, 4 and 6. This complicates things for me.

The total order values are:
Order 1 = £600
Order 2 = £1,200
Order 3 = £1,050
Order 4 = £1,100
Order 5 = £1,000
Order 6 = £1,200

I was originally looking for a formula to count the number of orders between a certain range, e.g. there are 4 orders over £1,000.

I am now looking for a formula that will sum the total of the orders in a given range, e.g. the sum of orders between £1,000 and £1,100 will be £3,150 (the total of orders 3, 4 and 5).

 

[Aladin Akyurek]

Please look at this screen shot
http://i.imgur.com/vi0aDAv.png
In cell F6 I want to calculate the total £cost of the 6 orders between £1001 and £1500

In the above example there are 6 orders. Some of the orders have multiple line items, e.g. orders 3, 4 and 6. This complicates things for me.

The total order values are:
Order 1 = £600
Order 2 = £1,200
Order 3 = £1,050
Order 4 = £1,100
Order 5 = £1,000
Order 6 = £1,200

I was originally looking for a formula to count the number of orders between a certain range, e.g. there are 4 orders over £1,000.

I am now looking for a formula that will sum the total of the orders in a given range, e.g. the sum of orders between £1,000 and £1,100 will be £3,150 (the total of orders 3, 4 and 5).

Would you post that sample here? An image is not readable into Excel cells...

 

[BrutalLogiC]

How do I post it?