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)*(Sheet 1!$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.


--
pie_terro
------------------------------------------------------------------------
pie_terro's Profile: http://www.excelforum.com/member.php...o&userid=35790
View this thread: http://www.excelforum.com/showthread...hreadid=555576

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
--
AP

"pie_terro" a écrit
dans le message de news:
...

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)*(Sheet 1!$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.


--
pie_terro
------------------------------------------------------------------------
pie_terro's Profile:
http://www.excelforum.com/member.php...o&userid=35790
View this thread: http://www.excelforum.com/showthread...hreadid=555576

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.


--
pie_terro
------------------------------------------------------------------------
pie_terro's Profile: http://www.excelforum.com/member.php...o&userid=35790
View this thread: http://www.excelforum.com/showthread...hreadid=555576

You could send me your faulty workbook at:

Cheers--
AP

"pie_terro" a écrit
dans le message de news:
...

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.


--
pie_terro
------------------------------------------------------------------------
pie_terro's Profile:
http://www.excelforum.com/member.php...o&userid=35790
View this thread: http://www.excelforum.com/showthread...hreadid=555576

I realized that this version works:

=SUMPRODUCT((Sheet1!$D$100:$D$800=Sheet3!E10)*(She et
1!$F$100:$F$800=Sheet2!$D$21)*(Sheet1!$H$100:$H$80 0))

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)*(Shee t
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 ?


--
pie_terro
------------------------------------------------------------------------
pie_terro's Profile: http://www.excelforum.com/member.php...o&userid=35790
View this thread: http://www.excelforum.com/showthread...hreadid=555576