Hi

the limitation of the SUMIF function that SUMPRODUCT is to overcome is that
the SUMIF function is limited to one criteria and can't be used to evaluate
multiple criteria ...

i tested a SUMPRODUCT statement similar to yours (but with a shorter sheet
name and no symbols in it) and it worked absolutely fine ...

=SUMPRODUCT(--('Sheet 4'!$A$1:$A$10=A3)*--('Sheet 4'!$D$1:$D$10))

so i don't know why yours didn't work

Cheers
JulieD

"MHoffmeier" wrote in message
...
Thanks, that worked well.

A3 is on the active sheet.

I had read that sumproduct overcame some limitations of sumif, so I have
been using it. when I try to reference another sheet, I get an error. I
am getting the syntax wrong somehow when I reference outside of the active
sheet

"JulieD" wrote in message
...
Hi

what sheet is the A3 on?

personally, i can't see anything wrong iwth that you've done, but i'm not
sure why you're using the SUMPRODUCT function for this, as you're only
testing one criteria the SUMIF should work just as well:

=SUMIF('Pal CluB Renov 2.2'!$A$1:$A$513,A3,'Pal CluB Renov
2.2'!$L$1:$L$513)

Cheers
JulieD


"MHoffmeier" wrote in message
...
I am using the following formula:
=SUMPRODUCT(--('Pal CluB Renov 2.2'!$A$1:$A$513=A3)*--('Pal CluB Renov
2.2'!$L$1:$L$513))
It works great if it is within the sheeet that is referenced, but does
not work if it is in another sheet in the same workbook. Where am I
going wrong?