It's a built-in limitation (for most array-processing functions) in versions
of Excel prior to XL2007 (the restriction was lifted in XL2007). However, it
is probably a good thing, otherwise the formula would end up doing
calculations for every row even when there is no data to be processed in
those rows... it is more efficient to limit array calculations as much as
possible to the cells where there is (or could be) actual data to process.

--
Rick (MVP - Excel)


"ruby" wrote in message
...
Hi

=SUMPRODUCT(--($B$1:$B$300=$P$1),--($C$1:$C$300=$H$2),$E$1:$E$300)

Worked perfectly, but i have another question, why do i need to specify a
range as in b1 to b300, why if i change to B:B and so on does this fail?

PS. Thanks for your help, your a legend!

"Rick Rothstein" wrote:

=SUMPRODUCT(--(Purchase!$B$1:$B$30=$K$1),--(Purchase!$C$1:$C$30=H2),Purchase!$E$1:$E$30)

Using the above formula, shouldn't the $K$1 reference be $K$2... your
data
starts on the 2nd row, correct? Also, if you are going to copy this
formula
down, you need to remove the $ signs (absolute reference) from $K$2 and
make
it K2 (that would make it like the H2 you have in the other part of the
expression). I would also change the references to Row 1 in the range to
Row
2. Give this formula a try and see if it works for you...


=SUMPRODUCT(--(Purchase!$B$1:$B$30=K2),--(Purchase!$C$2:$C$30=H2),Purchase!$E$2:$E$30)--Rick
(MVP - Excel)"ruby" wrote in
... Hi
Rick Tried you way but it didnt work! This is an example of the
spreadsheet. Date of Receipt Tax year Code Stock Amount Received
04/05/2004 2004 IVZ INVESCO 15.27 15/10/2004 2004 IVZ INVESCO 16.50
04/05/2005 2005 IVZ INVESCO 33.00 Code Security Total 2004 2005 IVZ
Invesco In Column K2 (2004) I want the total IVZ (B2)for 2004 (C2)
=SUMPRODUCT(--(B:B&C:C=2003&"IVZ"),E:E) - got
#NUM!=SUMPRODUCT(--(Purchase!$B$1:$B$30=$K$1),--(Purchase!$C$1:$C$30=H2),Purchase!$E$1:$E$30)
Neither worked? "Rick Rothstein" wrote: Two questions... Why didn't
you write it as "2003IVZ" instead of concatenating two constants? What
will your formula do if, for aparticular row, either Column A's cell =
"2003IVZ and Column C's cell is blank orvice versa?
-- Rick (MVP - Excel) "Shane Devenshire"
wrote in message
... Hi,
And if you really want to be cute:
=SUMPRODUCT(--(A8:A30&C8:C30=2003&"IVZ"),D8:D30) -- If this
helps, please click the Yes button Cheers, Shane Devenshire
"ruby" wrote: I have tried the SUMProduct but have been
unable to get it to work. I want to add column J if A = 2003 and
IVZ and so on for each year.
=SUMPRODUCT(--($A$8:$A$30="2003"),--($C$8:$C$30="IVZ"),$J$8:$J$30)
What am i doing wrong?