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?

"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 c=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?

Try changing "2003" to 2003

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ruby" wrote in message
...


"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 c=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?

If Column A has numbers in it, then you should test them against numbers
(2003), not a text string ("2003"). Try this...

=SUMPRODUCT(--($A$8:$A$30=2003),--($C$8:$C$30="IVZ"),$J$8:$J$30)

Now, if Column A contains Dates and not numbers (your mention of the word
'year' makes me wonder what is actually in Column A's cells), then maybe
this will work...

=SUMPRODUCT(--(Year($A$8:$A$30)=2003),--($C$8:$C$30="IVZ"),$J$8:$J$30)

--
Rick (MVP - Excel)


"ruby" wrote in message
...
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?

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?

Two questions... Why didn't you write it as "2003IVZ" instead of
concatenating two constants? What will your formula do if, for a particular
row, either Column A's cell = "2003IVZ and Column C's cell is blank or vice
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?

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 a particular
row, either Column A's cell = "2003IVZ and Column C's cell is blank or vice
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?

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

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?