From the help file under INDIRECT:

Syntax

INDIRECT(ref_text,a1)

A1 is a logical value that specifies what type of reference is contained in
the cell ref_text.

If a1 is TRUE or omitted, ref_text is interpreted as an A1-style reference.

If a1 is FALSE, ref_text is interpreted as an R1C1-style reference.


In this instance, FALSE and 0 have the same effect.


kwyjibo jones wrote:
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