| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
why do i get a #ref
hello all,
=SUM(IF(Sheet1:Sheet2!$A$2:$A$19=A2,IF(Sheet1:Shee t2!$B$2:$B$19=B2,IF(Sheet1:Sheet2!$C$2:$C$19=13,Sh eet1:Sheet2!$K$2:$K$19,0),0),0)) ctrl+shift+enter i was wondering if anyone could help me out with my #ref. i do believe it is because i am trying to evaluate from 2 sheets. i can get it to work from 1sheet. can anyone give any assistance? -- Thank You in advance, Brian |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
why do i get a #ref
i do believe it is because i am trying to evaluate from 2 sheets.
Yep! I think your best option is to break it out into 2 formulas, 1 for each sheet, then sum both results. Try these normally entered: =SUMPRODUCT(--(Sheet1!$A$2:$A$19=A2)--(Sheet1!$B$2:$B$19=B2),--(Sheet1!$C$2:$C$19=13),Sheet1!$K$19) =SUMPRODUCT(--(Sheet2!$A$2:$A$19=A2)--(Sheet2!$B$2:$B$19=B2),--(Sheet2!$C$2:$C$19=13),Sheet2!$K$19) -- Biff Microsoft Excel MVP "Zab" wrote in message ... hello all, =SUM(IF(Sheet1:Sheet2!$A$2:$A$19=A2,IF(Sheet1:Shee t2!$B$2:$B$19=B2,IF(Sheet1:Sheet2!$C$2:$C$19=13,Sh eet1:Sheet2!$K$2:$K$19,0),0),0)) ctrl+shift+enter i was wondering if anyone could help me out with my #ref. i do believe it is because i am trying to evaluate from 2 sheets. i can get it to work from 1sheet. can anyone give any assistance? -- Thank You in advance, Brian |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
why do i get a #ref
Ooops!
I left out some commas. Try these: =SUMPRODUCT(--(Sheet1!$A$2:$A$19=A2),--(Sheet1!$B$2:$B$19=B2),--(Sheet1!$C$2:$C$19=13),Sheet1!$K$19) =SUMPRODUCT(--(Sheet2!$A$2:$A$19=A2),--(Sheet2!$B$2:$B$19=B2),--(Sheet2!$C$2:$C$19=13),Sheet2!$K$19) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... i do believe it is because i am trying to evaluate from 2 sheets. Yep! I think your best option is to break it out into 2 formulas, 1 for each sheet, then sum both results. Try these normally entered: =SUMPRODUCT(--(Sheet1!$A$2:$A$19=A2)--(Sheet1!$B$2:$B$19=B2),--(Sheet1!$C$2:$C$19=13),Sheet1!$K$19) =SUMPRODUCT(--(Sheet2!$A$2:$A$19=A2)--(Sheet2!$B$2:$B$19=B2),--(Sheet2!$C$2:$C$19=13),Sheet2!$K$19) -- Biff Microsoft Excel MVP "Zab" wrote in message ... hello all, =SUM(IF(Sheet1:Sheet2!$A$2:$A$19=A2,IF(Sheet1:Shee t2!$B$2:$B$19=B2,IF(Sheet1:Sheet2!$C$2:$C$19=13,Sh eet1:Sheet2!$K$2:$K$19,0),0),0)) ctrl+shift+enter i was wondering if anyone could help me out with my #ref. i do believe it is because i am trying to evaluate from 2 sheets. i can get it to work from 1sheet. can anyone give any assistance? -- Thank You in advance, Brian |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
why do i get a #ref
thank you so much for your suggestion, i may go that route if i can not
figure something out. i do have this formula that does look up on multiple sheets BUT i can only have ONE criteria... bummer. if i could only combine the 2 i would have it. maybe you can come up with something for this? =SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!c9:c44 "),C3,INDIRECT("'"&SheetList&"'!r9:r44"))) -- Thank You in advance, Brian "T. Valko" wrote: Ooops! I left out some commas. Try these: =SUMPRODUCT(--(Sheet1!$A$2:$A$19=A2),--(Sheet1!$B$2:$B$19=B2),--(Sheet1!$C$2:$C$19=13),Sheet1!$K$19) =SUMPRODUCT(--(Sheet2!$A$2:$A$19=A2),--(Sheet2!$B$2:$B$19=B2),--(Sheet2!$C$2:$C$19=13),Sheet2!$K$19) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... i do believe it is because i am trying to evaluate from 2 sheets. Yep! I think your best option is to break it out into 2 formulas, 1 for each sheet, then sum both results. Try these normally entered: =SUMPRODUCT(--(Sheet1!$A$2:$A$19=A2)--(Sheet1!$B$2:$B$19=B2),--(Sheet1!$C$2:$C$19=13),Sheet1!$K$19) =SUMPRODUCT(--(Sheet2!$A$2:$A$19=A2)--(Sheet2!$B$2:$B$19=B2),--(Sheet2!$C$2:$C$19=13),Sheet2!$K$19) -- Biff Microsoft Excel MVP "Zab" wrote in message ... hello all, =SUM(IF(Sheet1:Sheet2!$A$2:$A$19=A2,IF(Sheet1:Shee t2!$B$2:$B$19=B2,IF(Sheet1:Sheet2!$C$2:$C$19=13,Sh eet1:Sheet2!$K$2:$K$19,0),0),0)) ctrl+shift+enter i was wondering if anyone could help me out with my #ref. i do believe it is because i am trying to evaluate from 2 sheets. i can get it to work from 1sheet. can anyone give any assistance? -- Thank You in advance, Brian |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
why do i get a #ref
It can be done with a single formula that's similar but it will be *a lot*
longer and **a whole lot more complex**. The easiest, shortest and less complex method would be to do what I did. You can combine those 2 formulas into a single if you want: =SUMPRODUCT1+SUMPRODUCT2 -- Biff Microsoft Excel MVP "Zab" wrote in message ... thank you so much for your suggestion, i may go that route if i can not figure something out. i do have this formula that does look up on multiple sheets BUT i can only have ONE criteria... bummer. if i could only combine the 2 i would have it. maybe you can come up with something for this? =SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!c9:c44 "),C3,INDIRECT("'"&SheetList&"'!r9:r44"))) -- Thank You in advance, Brian "T. Valko" wrote: Ooops! I left out some commas. Try these: =SUMPRODUCT(--(Sheet1!$A$2:$A$19=A2),--(Sheet1!$B$2:$B$19=B2),--(Sheet1!$C$2:$C$19=13),Sheet1!$K$19) =SUMPRODUCT(--(Sheet2!$A$2:$A$19=A2),--(Sheet2!$B$2:$B$19=B2),--(Sheet2!$C$2:$C$19=13),Sheet2!$K$19) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... i do believe it is because i am trying to evaluate from 2 sheets. Yep! I think your best option is to break it out into 2 formulas, 1 for each sheet, then sum both results. Try these normally entered: =SUMPRODUCT(--(Sheet1!$A$2:$A$19=A2)--(Sheet1!$B$2:$B$19=B2),--(Sheet1!$C$2:$C$19=13),Sheet1!$K$19) =SUMPRODUCT(--(Sheet2!$A$2:$A$19=A2)--(Sheet2!$B$2:$B$19=B2),--(Sheet2!$C$2:$C$19=13),Sheet2!$K$19) -- Biff Microsoft Excel MVP "Zab" wrote in message ... hello all, =SUM(IF(Sheet1:Sheet2!$A$2:$A$19=A2,IF(Sheet1:Shee t2!$B$2:$B$19=B2,IF(Sheet1:Sheet2!$C$2:$C$19=13,Sh eet1:Sheet2!$K$2:$K$19,0),0),0)) ctrl+shift+enter i was wondering if anyone could help me out with my #ref. i do believe it is because i am trying to evaluate from 2 sheets. i can get it to work from 1sheet. can anyone give any assistance? -- Thank You in advance, Brian |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
