why do i get a #ref

[Zab]

hello all,

=SUM(IF(Sheet1:Sheet2!$A$2:$A$19=A2,IF(Sheet1:Sheet2!$B$2:$B$19=B2,IF(Sheet1:Sheet2!$C$2:$C$19=13,Sheet1: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?

 

[T. Valko]

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)

 

[T. Valko]

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)

 

[Zab]

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")))

 

[T. Valko]

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

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")))