SUMPRODUCT works on first cell only...

[sahafi]

While this formula works on another sheet, it doesn't work on this sheet.
Actually it works only on first cell. I have tried copy > paste special
formula, I also tried drag the formula down, but nothing seem to work. I have
checked the data format for each column, I even used 'substitute with char
160, but nothing will work. Here's my formula:

SUMPRODUCT(--(Data!$A$2:$A$12000=Report!$C$1)*(Data!$B$2:$B$12000=Report!$A10)*(Data!$C$2:$C$12000=Report!$B10)*(Data!$E$2:$E$12000=Report!$C$2)*(Data!$F$2:$F$12000))

'Report' is where my formula is, and 'Data' sheet where's my data resides.

Any help is greatly appreciated.

Oz

Thanks.

 

[lk]

Check your absolute/relative reference, they are not consistent. Also, if
you put the "--" in front of each test (so all but the last one), I think the
formula works better.

 

[Allllen]

Does this do what you want?

=SUMPRODUCT(--(Data!$A$2:$A$12000=Report!$C$1)*(Data!$B$2:$B$12000=Report!$A$10)*(Data!$C$2:$C$12000=Report!$B$10)*(Data!$E$2:$E$12000=Report!$C$2)*(Data!$F$2:$F$12000))

check it carefully and you will see it is not the same as yours.
I have added two strategic $s.
(Report!$A$10, Report!$B$10)

 

[sahafi]

Thank you both. Actually (Report!$A10, Report!$B10) is how I wanted it to be.
Those are the only two variable criteria (A10, A11, A12, etc). I got it to
work simply by typing over the data on the report sheet again. Because when I
changed the data format for those columns to match the imported data (Data
sheet), the new format will not take effect (don't know why) untill I retype
the data again. Once I have done that, my original formula worked as is.

Once again thank you.