Formula to sum multiple criteria and multiple

[Mr Spreadie]

Hi all,

I'm curious to see if anyone's got any bright ideas about how to simply a sum formula I've created. I'm trying to sum figures in the range of columns O:R on a WS named 'Daily', where the only rows considered are text values from the range B6:B12 that are also in column D on the Daily WS. I've manged to make it work using the following formula:

=SUMPRODUCT(SUMIF(Daily!B:B,S6:S12,Daily!O:O)+SUMIF(Daily!B:B,S6:S12,Daily!P:P)+SUMIF(Daily!B:B,S6:S12,Daily!Q:Q)+SUMIF(Daily!B:B,S6:S12,Daily!R:R))

I was hoping to simply it and attempted the following:

=SUMPRODUCT(SUMIF(Daily!B:B,S6:S12,Daily!O:R))

However, the above SUMPRODUCT function only allows the critera (S6:S12) to become a range but not the sum range (Daily!O:R) too.

Any bright spark have any clues how to get the simplified version working?

Thanks in advance!

 

[sheetspread]

Like this?

Excel 2010
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
6	Q													33	62	36	33
7	W													61	58	83	43
8	E													94	47	42	6
9	R													48	35	71	83
10	T													4	33	63	69
11	Y													57	2	66	42
12	U													8	30	64	68
13
14
15	E
16	U
17	Y
18		526
Sheet13

Cell Formulas
Range	Formula
C18	=SUMPRODUCT(COUNTIF($B$15:$B$17,$B$6:$B$12)*$O$6:$R$12)

 

[Mr Spreadie]

Thank you for your reply.

That worked to an extent where I specified an exact range in all instances. However the data in the table will change daily where I could encounter X number of rows. Therefore, to alter your example slightly, I would need something like:

(moving the criteria to column U)


=SUMPRODUCT(COUNTIF($U$15:$U$17,$B:$B)*$O:$R)


Because the above contains ranges that include blank cells then I receive a #VALUE! result.

Any thoughts on how to correct this?

 

[sheetspread]

It worked when I tried:

Excel 2010
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
6	Q													33	62	36	33
7	W													61	58	83	43
8	E													94	47		6
9	R													48	35	71	83
10	T													4	33	63	69
11	Y													57		66	42
12	U													8	30	64	68
13
14
15																				R
16																				U
17																				Y
18		572
Sheet14

Cell Formulas
Range	Formula
C18	=SUMPRODUCT(COUNTIF($U$15:$U$17,B:B)*O:R)

but is very slow because you're including all the rows. An index formula that counts would be much better for such a variable range.