SUMPRODUCT returning incorrect result

[sahafi]

Hi,

I'm using sumproduct and also index, match formulas/function with correct
result in some area of my workbook, but with incorrect result in another area
of the workbook.
When I use:
SUMPRODUCT(--(sheet1!col1=sheet2$bw$9)*(sheet1!col2=sheet2$A11)*........(sheet1col6)) I only get a zero '0' back.

When I use:
SUMPRODUCT(--(sheet1!col1=sheet2$bw$9)--(sheet1!col2=sheet2$A11)--........(sheet1col6))
I get the total for the entire column (col6) instead of the subtotal that
matches those 4 criteria.
I also tried INDEX(sheet1col6), MATCH(................)) But this one grab
only the value on the first row instead of summing up 10 or more rows.

All 4 columns on sheet1 are formatted as text (imported from Access), while
col6 as number and they match same format on sheet2. Like I said, both
formulas working fine on different data, but not here. I just couldn't figure
it out. I have tried copying a blank cell and do paste special/add but it
didn't help either. Any direction on this is very much appreciated.

Thanks.

 

[Bob Phillips]

Note that SUMPRODUCT doesn't work with complete columns, you have to specify
a range.

And all sheets are terminated by a ! to show it is a sheet name

SUMPRODUCT(--(sheet1!A1:A1000=sheet2!$bw$9)*(sheet1!b1:B1000=sheet2!$A11)*..
.......(sheet1!F1:F1000))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Hi,

I'm using sumproduct and also index, match formulas/function with correct
result in some area of my workbook, but with incorrect result in another area
of the workbook.
When I use:

SUMPRODUCT(--(sheet1!col1=sheet2$bw$9)*(sheet1!col2=sheet2$A11)*........(she
et1col6)) I only get a zero '0' back.

 

[sahafi]

Yes, I'm aware of that. Actually all my ranges are from: A2:A20500,
B2:B20500, etc. Just for the sake of typing I included col1, col2, etc. To
avoid making mistake, I usually click on the sheet tab that I would like to
get the data from, so all of them do have '!' after the name. Like I said the
formula does return value, just not the correct value.

 

[Bob Phillips]

Well my experience with SP is that if the answer is wrong, you either made a
mistake in the formula, or the data is wrong. There is insufficient
information to elaborate any further.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[sahafi]

Thanks for trying.

I went through the formula with the fine-tooth comb, letter by letter, and
digit by digit. All look good. It's most likely has to do with the data, but
I have checked it more than 6 times.
May be someone who has gone through a similar situation can shed some light
on how to tackle this.

Thanks.

 

[Roger Govier]

Hi

If you want to mail me a copy of the sheet direct, I will take a look to
see if I can spot what is wrong.
Omit NOSPAM from my address to mail direct.

 

[sahafi]

Here's the formula
=SUMPRODUCT(--(Cuts!$A$2:$A$22444=Model!$C$8)*(Cuts!$B$2:$B$22444=Model!$A11)*(Cuts!$C$2:$C$22444=Model!$B11)*(Cuts!$H$2:$H$22444=Model!$C$9)*(Cuts!$F$2:$F$22444)).
Columns: A, B, C, and H are formatted as 'text' while Column F is number,
and they match their corresponding cells in the 'Model' sheet. The data in
the 'Cuts' sheet has been imported into Excel from Business Objects. The same
formula is working on data that has been imported from MS Access, but not on
this. Currently i'm getting '#N/A' even though there's data the matches the
criteria above, but the formula can't get it.

Thanks.

 

[Roger Govier]

Hi

Try breaking the formula down into the 5 constituent parts and see which
is returning the error e.g.
=SUMPRODUCT(--(Cuts!$A$2:$A$22444=Model!$C$8))

Then, if it is one of the Text ones, try doing
=LEN(cell) where cell is the cell containing data being matched, from
Model, and on one of the cells you think should match from Cuts.
You may find there are extra spaces or hidden Non-breaking spaces
Char(160) in the data that has been imported.

David McRitchie has a TRIMALL function on his site that is useful for
cleaning up data
http://www.mvps.org/dmcritchie/excel/join.htm#trimall