sumif with a second condition

[jewel]

Help please! I'm a beginner here and I have a working sumif function to
which I need to add a second condition. This second condition is to only sum
if the text in column A is a certain 3-letter code. The closest I've gotten
is a #value error that will work if I limit the text condition to just one
cell. I need the second condition to work over a range of cells. Is this
even the right approach?

=IF(A74="adr",SUMIF($D962:$D1001,"=f",$F962:$F1001))

the above formula works; however I need A74="adr" to look at the range
A74:A1001 and apply the sumif to all the adr's that meet the sumif criteria.
Hopefully, this makes sense to someone who can help. TIA!!!

 

[Dave Peterson]

I would think you'd want to look at A962:A1001

=sumproduct(--($a962:$a1001="adr"),--($d962:$d1001="f"),($f962:$f1001))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

=======
And if you're using xl2007, take a look at =sumifs() in excel's help.

 

[Roger Govier]

Hi

Try
=SUMPRODUCT(($A$74:$A$1001="adr")*($D$2:$D$1001="f")*$F$2:$F$1001)

Note all ranges have to be of equal length.

 

[Roger Govier]

It might have helped if I had typed ranges of equal length!!!
Those blessed fingers have a mind of their own sometimes<bg>

=SUMPRODUCT(($A$74:$A$1001="adr")*($D$74:$D$1001="f")*$F$74:$F$1001)

 

[jewel]

Thanks so much for the response. The thing is I do want to look at A74:A1001
b/c I have 17 other three letter codes that are part of the second condition.
And I have 9 other conditions besides the "f" inside the sumif. And my 17
3-letter codes have a varying number of rows in them. For instance, I may
have 20 rows of ADR and then 15 rows of RBH, where 7 of the ADR rows are "f"
and need to be summed, but only 4 rows of RBH are "f" and also need to be
summed in another cell. Makes my head spin just trying to explain it....

 

[jewel]

Thanks for the quick response! Part of my issue is that the ranges of the
second condition (the 3-letter code) are *not* equal to the ranges -- they
will vary month to month, so I was looking for an elegant way for Excel to
pick up the two criteria without having to add extra blank rows each month
and creating absolute cell references for each three-letter code.

 

[jewel]

Thank you both! I played a bit more and got it to work -- now to copy it
correctly to the 10 gazillion cells...