SUMIF with 2 criteria

  • Thread starter Thread starter ruby
  • Start date Start date
R

ruby

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 said:
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?
Click to expand...
 
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)
 
Hi,

And if you really want to be cute:

=SUMPRODUCT(--(A8:A30&C8:C30=2003&"IVZ"),D8:D30)
 
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?
 
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?
 
=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" <[email protected]> wrote in messageHi 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" <[email protected]> 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!
 
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.
 
Back
Top