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

 

[Marcelo Branco]

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).

Maybe

	A​	B​	C​	D​	E​
1​	Order#​	Descripition​	Value​		1000 <= Orders <= 1100​
2​	1​	Desc1​	600​		3150​
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(A2:A11,A2:A11,C2:C11)>=1000),--(SUMIF(A2:A11,A2:A11,C2:C11)<=1100),--(MATCH(A2:A11,A2:A11,0)=ROW(A2:A11)-ROW(A2)+1),SUMIF(A2:A11,A2:A11,C2:C11))

Hope this helps

M.