Problem with sumproduct, possibly formatting?

[finchamcr]

I have a spreadsheet(WB1) which extracts information from another
database. The spreadsheet has columns A(alpha/numeric data formatted
as General), B(alpha/numeric data formatted as General), and C(numeric
data formatted as Numeric). In another spreadsheet(WB2), I need to sum
column C in WB1 when specified criteria is met from WB2. The array
formula I have typed in WB2T2 is
={SUMPRODUCT((WB1$A$1:$A$500=WB1A1)*(WB1$B$2:$B500=WB2G1)*(WB1$C$1:$C$500))}.
For some reason the formula is always returning 0; however, if I copy a
cell from WB1A1 and paste it in WB2A1, the formula returns the correct
value. I thought it might me a formatting problem but I have verified
that all the columns in both spreadsheets have the same formatting.
Could it be because the data in the database that I am extracting the
information from has assigned formatting and excel does not transfer it
over?? I could copy and paste all the items to make the formula work
but it is a large spreadsheet with over 10,000 rows. Does anyone have
any idea of how to help?

 

[Biff]

Hi!

When you say spreadsheet, do you mean worksheet?

Are the names WB1 and WB2 worksheets in the same workbook?

The formula is missing the correct names for these.

If WB1 and WB2 are worksheets in the same workbook, try
this:

=SUMPRODUCT(--(WB1!A1:A500=WB1!A1),--(WB1!B1:B500=G1),WB1!
C1:C500)

No need to array enter. Also, the ranges must be exactly
the same size. One of yours was different(typo?)

Biff

 

[Jerry W. Lewis]

It is always better to copy your formula from a cell and paste it into
your post instead of retyping. Without seeing your formula we can only
guess at what is going on.

Retyping may introduce new errors while correcting the actual error.
For instance, a copied formula would not included the curly brackets
(which are not properly placed) and would have a valid separator between
sheet name and cell reference. Your three arrays are also not the same
size, which must be different than the original since you say the
original returns 0 instead of #N/A.

Explicitly multiplying the logical arrays (as you did) is perfectly
valid (as is Biff's suggestion that instead you separately coerce each
to numeric with -- and then separate with commas).

Jerry