Formula question

P

pie_terro

Hi,

I have this formula:

=SUMPRODUCT((Sheet1!$D$5:$D$96=Sheet3!E10)*(Sheet1!$F$5:$F$96=Sheet2!$D$21)*(Sheet1!$H$5:$H$96))

but when I make the range larger:

=SUMPRODUCT((Sheet1!$D$5:$D$800=Sheet3!E10)*(Sheet1!$F$5:$F$800=Sheet2!$D$21)*(Sheet1!$H$5:$H$800))

it doesn't work anymore... Any idea ? Is SUMPRODUCT limited in the
number of raws ?

Thanks a lot in advance.
 
A

Ardus Petus

Does not work means
returns no results
returns wronk results
returns an error code ?

There is no limitation in the number of rows SUMPRODUCT can handle.

HTH
 
P

pie_terro

Actually, it means that the cell doesn't return any result. (just 0.00)

Excel tells me that the formula has a circular reference and the error
seams to be Sheet3!E10 (formula is on Sheet3).

I made a new file - the same - containing less raws on Sheet1. It works
perfectly, without saying there is a circular reference.

For info, Sheet3!E10 is text.
 
P

pie_terro

I realized that this version works:

=SUMPRODUCT((Sheet1!$D$100:$D$800=Sheet3!E10)*(Sheet
1!$F$100:$F$800=Sheet2!$D$21)*(Sheet1!$H$100:$H$800))

and this version too:

=SUMPRODUCT((Sheet1!$D$10:$D$99=Sheet3!E10)*(Sheet
1!$F$10:$F$99=Sheet2!$D$21)*(Sheet1!$H$10:$H$99))

but this version doesn't work:

=SUMPRODUCT((Sheet1!$D$99:$D$800=Sheet3!E10)*(Sheet
1!$F$99:$F$800=Sheet2!$D$21)*(Sheet1!$H$99:$H$800))

as if Excel could recognize and misinterpret that the number of raws is
between 10 and 99 (or xx and xx) and 100 and 999 (or xxx and xxx).

Any thought ?
 
Top