Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7
Default 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!!!
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default 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
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,480
Default 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!!!


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,480
Default 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!!!


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7
Default 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




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7
Default 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!!!


  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumif condition pdberger Excel Worksheet Functions 2 June 24th 08 08:06 PM
SumIF for more than one condition kippers Excel Worksheet Functions 3 October 26th 07 12:13 PM
Sumif, having two condition ViestaWu Excel Worksheet Functions 2 June 7th 07 10:23 AM
sumif with or< condition Will Fleenor Excel Discussion (Misc queries) 8 April 27th 07 07:45 AM
sumif on more than one condition steve alcock Links and Linking in Excel 4 May 13th 05 01:53 PM


All times are GMT +1. The time now is 07:11 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"