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
|