Home 
Search 
Today's Posts 
#1




SUMIF HELP
Can you have two arguments for a SUMIF function? I want cell K56 to sum
cells K3:K44 if cells A3:A44 has a value of M or W. I can it to work with just one value but Im unsure of how to get it to do two. Thanks, Chance 
#2




=SUMPRODUCT((A3:A44={"M","W"}),K3:K44)
 HTH RP (remove nothere from the email address if mailing direct) "Chance224" wrote in message ... Can you have two arguments for a SUMIF function? I want cell K56 to sum cells K3:K44 if cells A3:A44 has a value of M or W. I can it to work with just one value but I'm unsure of how to get it to do two. Thanks, Chance 
#3




Using SUMPRODUCT offers the most flexibility, but in this=20
case you could use: =3DSUM(SUMIF(A3:A44,{"M","W"},K3:K44)) HTH Jason Atlanta, GA Original Message Can you have two arguments for a SUMIF function? I want=20 cell K56 to sum=20 cells K3:K44 if cells A3:A44 has a value of M or W. I=20 can it to work with=20 just one value but I=E2?Tm unsure of how to get it to do=20 two. Thanks, Chance . 
#4




Try in K56:
=SUMPRODUCT(((A3:A44="M")+(A3:A44="W")),K3:K44)  Rgds Max xl 97  GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom  "Chance224" wrote in message ... Can you have two arguments for a SUMIF function? I want cell K56 to sum cells K3:K44 if cells A3:A44 has a value of M or W. I can it to work with just one value but I'm unsure of how to get it to do two. Thanks, Chance 
#5




"Bob Phillips" wrote
=SUMPRODUCT((A3:A44={"M","W"}),K3:K44) Tried this, Bob, but think it returns #VALUE!  Rgds Max xl 97  GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom  
#6




I think Bob meant this:
=3DSUMPRODUCT((A3:A44=3D{"M","W"})*K3:K44) Jason Original Message "Bob Phillips" wrote =3DSUMPRODUCT((A3:A44=3D{"M","W"}),K3:K44) Tried this, Bob, but think it returns #VALUE!  Rgds Max xl 97  GMT+8, 1=B0 22' N 103=B0 45' E xdemechanik <atyahoo<dotcom  . 
#7




Kindly use the formula in the following manner.
=SUMIF(A3:A6,"m",K3:K6)+SUMIF(A3:A6,"w",K3:K6)+SUM IF(A3:A6,"j",K3:K6) Good luck..john britto "Chance224" wrote: Can you have two arguments for a SUMIF function? I want cell K56 to sum cells K3:K44 if cells A3:A44 has a value of M or W. I can it to work with just one value but Im unsure of how to get it to do two. Thanks, Chance 
#8




=SUMPRODUCT((A3:A44={"M","W"})*K3:K44)
is not efficient though. In cases of 2 conditions to or, one can get away with the + idiom: [1] =SUMPRODUCT((A3:A44="M")+(A3:A44="W"),K3:K44) as Max suggested. The following invokes an efficient idiom for or'ing... [2] =SUMPRODUCT(ISNUMBER(MATCH(A3:A44,{"M","W"},0)),K3:K44) An equivalent setup with SumIf is... [3] =SUMPRODUCT(SUMIF(A3:A44,{"M","W"},K3:K44)) where Sum can be sustituted for SumProduct when a constant array of conditions is used (as occurs in your other reply). To recap, with J1:J2 housing the conditions "M" and "W"... [1] SUMPRODUCT((A3:A44=J1)+(A3:A44=J2),K3:K44) [2] SUMPRODUCT(ISNUMBER(MATCH(A3:A44,J1:J2,0)),K3:K44) [3] SUMPRODUCT(SUMIF(A3:A44,J1:J2,K3:K44)) The first one becomes unwieldy with more conditions. It would be interesting to compare temporal profiles of the second and the third though. Jason Morin wrote: I think Bob meant this: =SUMPRODUCT((A3:A44={"M","W"})*K3:K44) Jason Original Message "Bob Phillips" wrote =SUMPRODUCT((A3:A44={"M","W"}),K3:K44) Tried this, Bob, but think it returns #VALUE!  Rgds Max xl 97  GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom  . 
#9
Posted to microsoft.public.excel.misc




SUMIF HELP
On Tuesday, March 15, 2005 at 10:25:27 PM UTC+5:30, Bob Phillips wrote:
=SUMPRODUCT((A3:A44={"M","W"}),K3:K44)  HTH RP (remove nothere from the email address if mailing direct) "Chance224" wrote in message ... Can you have two arguments for a SUMIF function? I want cell K56 to sum cells K3:K44 if cells A3:A44 has a value of M or W. I can it to work with just one value but I'm unsure of how to get it to do two. Thanks, Chance =SUMIF(K3:K6,"0<") 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
How to use SUMIF to return sums between two values located in cells  Excel Worksheet Functions  
How do I use the sumif if I have multiple criteria (i.e. greater .  Excel Worksheet Functions  
SUMIF Not  Excel Discussion (Misc queries)  
SUMIF  Range name to used for the "sum_range" portion of a SUMIF function  Excel Worksheet Functions  
SUM(IF( Array to avoid #NUM! values  Excel Worksheet Functions 