Multiple Sum If

[BenDoc08]

Hey All

In Column C I have a State, in column G I have a number and in column I I have a cost price. I'm trying to sum cost prices for instances where G and I have specific values. Is there a non VBA way to achieve this?

Thanks in advance,
Ben

 

[Trebor76]

Hi Ben,

Have a look at the following formula that sums the amounts in I2:I15 where the text in C2:C15 is equal to "NSW" and the the value in G2:G15 is equal to 2:

=SUMPRODUCT(--(C2:C15="NSW"),--(G2:G15=2),--(I2:I15))

HTH

Robert

 

[gnrboyd]

Trebor76 beat me to it.....but here is an example. If you have 2007 or higher (I think) you can use the function SUMIFS to do the same thing. Just search the help files in Excel for the details. I'm still using an older version that doesn't have this function so I use the SUMPRODUCT function.

Hope this helps.

Book1
	C	D	E	F	G	H	I	J	K	L
1	State				Number		Cost		State:	MO
2	MO				20		5.00		Number	20
3	MO				15		6.00		Total	20.00
4	KS				10		4.00
5	KS				10		8.00
6	IA				15		4.00
7	MO				20		6.00
8	KS				20		4.00
9	MO				20		9.00
10	MO				10		7.00
11	KS				25		9.00
12	IA				20		8.00
13	MO				15		9.00
14	NE				20		7.00
15	KS				20		2.00
16	MO				15		9.00
Sheet1

 

[Trebor76]

Trebor76 beat me to it

I'm glad you still posted though as "a picture tells a thousand words" and you added another lead for Ben to follow (if he has 2007 that is).

 

[BenDoc08]

Thanks for your help guys. It's much appreciated. And thanks gnrboyd for posting a visual too.

I'm happy to say the formula works, though with just one slight problem. It only works for me up to row9999. Once I try and put in row limits over 9999 I get an N/A instead.

I do have Excel 2007 though so will investigate boyd's idea.

Cheers again for your help, it's much appreciated.

 

[BenDoc08]

Happy to confirm sumifs work.

Using Trebor's initial example this is the formula I used:

=SUMIFS(I:I,C:C,"NSW",G:G,2)

When using the SUMIFS feature you put the sum range first, then the criterias after that, in the syntax of criteria range, criteria.

Thanks agan guys.

 

[gnrboyd]

There shouldn't be any problem with going past row 9999. Make sure all of your ranges in the formula have the same beginning and ending row. Otherwise, you will get errors. If you still have trouble with it, post the exact formula and someone can take a look.

"a picture tells a thousand words"

I agree. I am visual in nature and examples always help me.

 

[Trebor76]

=SUMIFS(I:I,C:C,"NSW",G:G,2)

With the massive number of rows (1,048,576) avaiable in 2007 and beyond, try not to reference entire columns.