SUMPRODUCT works on first cell only...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sahafi - 17 Nov 2006 17:19 GMT

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.

Signature

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

Reply to this Message

lk - 17 Nov 2006 17:26 GMT

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.

> 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
[quoted text clipped - 11 lines]
>
> Thanks.

Reply to this Message

Allllen - 17 Nov 2006 17:29 GMT

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)

Signature

Allllen

> 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
[quoted text clipped - 11 lines]
>
> Thanks.

Reply to this Message

sahafi - 21 Nov 2006 19:41 GMT

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.

Signature

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

> Does this do what you want?
>
[quoted text clipped - 19 lines]
> >
> > Thanks.

Reply to this Message