sumif with a second condition
 

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

sumif with a second condition
 

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 he
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.

jewel wrote:

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

sumif with a second condition
 

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.
--
Regards
Roger Govier

"jewel" wrote in message
...
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!!!

sumif with a second condition
 

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)

--
Regards
Roger Govier

"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
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.
--
Regards
Roger Govier

"jewel" wrote in message
...
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!!!

sumif with a second condition
 

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

"Dave Peterson" wrote:

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 he
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.

jewel wrote:

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

sumif with a second condition
 

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.

"Roger Govier" wrote:

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.
--
Regards
Roger Govier

"jewel" wrote in message

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

sumif with a second condition
 

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

"Dave Peterson" wrote:

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 he
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.

jewel wrote:

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