Sumproduct sheet reference

[MHoffmeier]

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?

 

[JulieD]

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

 

[Peo Sjoblom]

When you say it does not work what do you mean?
If it does not calculate you might have the other sheets formatted as text
Note that the criteria is in A3 on the sheet that holds the formula
Also, the formula can be written as

=SUMPRODUCT(--('Pal Club Renov 2.2'!$A$1:$A$513=A3),'Pal Club Renov
2.2'!$L$1:$L$513)

No need to do the multiplation since you already are using the unary minuses


Regards,

Peo Sjoblom

 

[MHoffmeier]

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]

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