That works, thanks. Why do you need a ,0 at the end of the formula?

Regards,
kwyjibo

On Aug 13, 5:50*pm, Glenn wrote:
See if this works:

=SUMPRODUCT(SUMIF(INDIRECT($M$1:$M$20&"!$B$3:$B$52 "),Total!$A3,
INDIRECT($M$1:$M$20&"!R3C"&COLUMN()&":R52C"&COLUMN (),0)))

kwyjibo jones wrote:
Thanks for all the input, it's much appreciated. *However, I'm still
not there yet.

SeanTimmons response works-
=SUMPRODUCT(SUMIF(INDIRECT($M$1:$M$20&"!$B$3:$B$52 "),Total!$A3,INDIRECT
($M$1:$M$20&"!C$3:C$52")))

However, it is not quite there yet.

If you see from the last part of the function above, "!C$3:C$52",
there is no $ in front of the C. *When I replicate the formula across
the row, I would like the C column to change, but the B column above
to remain constant.

I cannot seem to do this. *Is it possible to take the "!C$3:C$52" bit
outside of the INDIRECT function and still have it work?

Regards,
kwyjibo

On Aug 13, 4:39 pm, Sean Timmons
wrote:
From Bob Phillips post:

Put the sheet names in M1:M20 and use

=SUMPRODUCT(SUMIF(INDIRECT($M$1:$M$20&"!$B$3:$B$52 "),Total!$A3,INDIRECT($M$1:$M$20&"!C$3:C$52")) )

"kwyjibo jones" wrote:
Take a look at the following formula
=SUMIF('DP180'!$B$3:$B$52,Total!$A3, 'DP180'!C$3:C$52)
Here, I am searching down the B column in sheet DP180 for values
matching Total!A3. *I then sum the corresponding numbers in the C
column.
I would like to do this across multiple sheets - however, the only way
I can do so, is
=SUM(SUMIF('DP180'!$B$3:$B$52,Total!$A3, 'DP180'!C$3:C$52),SUMIF
('DP181.....))
There must be a more elegant way to do this. *I've tried putting a
range of sheets into the SUMIF, but that doesn't work. *Quick googling
seems to suggest SUMPRODUCT, but I'm not sure how that works, and even
copying simple examples to test SUMPRODUCT results in errors.
Regards,
kwyjibo