Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sumproduct sheet reference
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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" wrote: 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? |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need formula for sheet & cell reference | Excel Worksheet Functions | |||
Reference another sheet... | Excel Discussion (Misc queries) | |||
Cell reference problems with Summary sheet | Excel Worksheet Functions | |||
Cell reference - for the sheet name, can I use a variable? | Excel Discussion (Misc queries) | |||
Naming & renaming a sheet tab | Excel Worksheet Functions |