SUMPRODUCT works on first cell only...
 

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$120 00=Report!$A10)*(Data!$C$2:$C$12000=Report!$B10)*( Data!$E$2:$E$12000=Report!$C$2)*(Data!$F$2:$F$1200 0))

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

Any help is greatly appreciated.

Oz

Thanks.
--
when u change the way u look @ things, the things u look at change.

SUMPRODUCT works on first cell only...
 

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.

"sahafi" wrote:

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$120 00=Report!$A10)*(Data!$C$2:$C$12000=Report!$B10)*( Data!$E$2:$E$12000=Report!$C$2)*(Data!$F$2:$F$1200 0))

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

Any help is greatly appreciated.

Oz

Thanks.
--
when u change the way u look @ things, the things u look at change.

SUMPRODUCT works on first cell only...
 

Does this do what you want?

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

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)

--
Allllen


"sahafi" wrote:

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$120 00=Report!$A10)*(Data!$C$2:$C$12000=Report!$B10)*( Data!$E$2:$E$12000=Report!$C$2)*(Data!$F$2:$F$1200 0))

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

Any help is greatly appreciated.

Oz

Thanks.
--
when u change the way u look @ things, the things u look at change.

SUMPRODUCT works on first cell only...
 

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.

--
when u change the way u look @ things, the things u look at change.


"Allllen" wrote:

Does this do what you want?

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

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)

--
Allllen


"sahafi" wrote:

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$120 00=Report!$A10)*(Data!$C$2:$C$12000=Report!$B10)*( Data!$E$2:$E$12000=Report!$C$2)*(Data!$F$2:$F$1200 0))

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

Any help is greatly appreciated.

Oz

Thanks.
--
when u change the way u look @ things, the things u look at change.