Formula question

[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.

 

[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

 

[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.

 

[Ardus Petus]

You could send me your faulty workbook at: (e-mail address removed)

Cheers--
AP

"pie_terro" <[email protected]> a écrit
dans le message de (e-mail address removed)...

 

[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 ?