Multi criteria & return sum

[AronTravis]

Hi there,

I am seeking a solution to my problem as per below:

I have a sales rep spreadsheet of products sold, quantities, rep name etc & I am seeking a formula to search column A3:A400 [Date Column] for a period of time (I.e. 13 Feb - 19 Feb 2012), search column B3:B400 [State Column] for the state (I.e. QLD), search column J3:J400 [Product Column] for a specific product (I.e. "Bun") and if these conditions are met then return the sum of all matching rows from M3:M400 [Product Weight Column]

I hope someone is able to provide some insight into this query as I'm generally quite strong with Excel formulae, however cannot produce this for the life of me!


Thanks,
Aron

 

[RonB1111]

Is there any reason you don't want to do this with auto filters turned on and a subtotal formula in M401: =subtotal(9,M3:M400)?

Another approach is to use Microsoft Query - if you're interested let me know.

 

[AronTravis]

Thanks for the quick reply...

I'm hoping to find a formula as the spreadsheet is distributed among sales reps who send the spreadsheet back weekly.

My aim is to lock hidden tabs which I can then unlock, view and gain a quick snapshot of the summarised data in a graph/chart.

The spreadsheet data can be customised based on set promotions. So I don't mind spending a little time to make sure this works fluently.

 

[Dave Patton]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	13 Feb 2012
2	19 Feb 2012	Qld	Result	30						Bun
3
4	Date	State								Product	Weight
5	05 Feb 2012	Qld								Bun	22
6	10 Feb 2012	Qld								Bun	24
7	15 Feb 2012	ABC								Bun	26
8	17 Feb 2012	Qld								XYZ	28
9	19 Feb 2012	Qld								Bun	30
10	21 Feb 2012	ABC								Bun	32
1a

Excel 2003

Cell Formulas
Range	Formula
D2	=SUMPRODUCT(--(A5:A310>=A1),--(A5:A310<=A2),--(B5:B310=B2),--(J5:J310=J2),(K5:K310))

Try SumProduct

I put the criteria and result in the first two lines and I used Column K for the weight so the info could be copied to the message area. Edit ranges and columns to actual.

If you prefer, you can enter the criteria within the formula.