sumproduct across worksheets

[freebee]

Hi, I have the following formula in cell A1, sheet 1:
=sumproduct--('sheet2'!a1:a10="apple"),--('sheet2'!b1:b10="orange"),--('sheet2'!c1:c10)
1.how do I simplify this formula
2.can i include another sumproduct formula from sheet 3
Pls. help
Thanks.

 

[David Biddulph]

1. That formula isn't valid. You're missing some of the parentheses.
Perhaps you intended to say
=SUMPRODUCT(--(Sheet2!A1:A10="apple"),--(Sheet2!B1:B10="orange"),--(Sheet2!C1:C10))
or
=SUMPRODUCT((Sheet2!A1:A10="apple")*(Sheet2!B1:B10="orange")*(Sheet2!C1:C10))
?
2.. Yes.

 

[Eduardo]

Hi

=SUMPRODUCT(--(Sheet2!A1:A10="apple"),--(Sheet2!B1:B10="orange"),Sheet2!C1:C10)

to your 2nd question regarding sheet 3

=SUMPRODUCT(--(Sheet2!A1:A10="apple"),--(Sheet2!B1:B10="orange"),Sheet2!C1:C10)
+
SUMPRODUCT(--(Sheet3!A1:A10="apple"),--(Sheet3!B1:B10="orange"),Sheet3!C1:C10)

if this helps please click yes, thanks

 

[freebee]

Hi,thanks for the reply.
Sheet 3 is a file I copied from other workbook. When I add sheet 3, it
keeps asking me to update values by selecting a file. I tried to retrieve
sheet 3 and save as a new file then move to my master workbook, still does
not work. Anything I'm not doing right?
Thanks.

 

[freebee]

Hi,thanks for the reply.
Sheet 3 is a sheet I copied from other workbook. When I tried to add
sumproduct formula from sheet 3, a window always pops up asking me to update
value by selecting files from the directory. I tried to copy sheet 3, save as
new file, move it back to my master workbook, still does not work. Anything
I'm not doing right?
Thanks.