If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#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 I’m 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 atyahoodotcom ---- "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 atyahoodotcom ---- |
#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 atyahoodotcom ---- . |
#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 I’m unsure of how to get it to do two. Thanks, Chance |
#8
|
|||
|
|||
"Jason Morin" wrote
I think Bob meant this: =SUMPRODUCT((A3:A44={"M","W"})*K3:K44) Thanks, Jason ! Yes, I'm quite sure he meant that g -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik atyahoodotcom ---- |
#9
|
|||
|
|||
=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 atyahoodotcom ---- . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to use SUMIF to return sums between two values located in cells | ScottBerger | Worksheet Functions | 1 | November 18th, 2004 07:09 PM |
SUMIF vs SUM(IF(..)) vs SUMPRODUCT | Harlan Grove | Worksheet Functions | 7 | July 1st, 2004 10:22 PM |
SUMIF vs SUM(IF(..)) vs SUMPRODUCT | Harlan Grove | General Discussion | 7 | July 1st, 2004 10:22 PM |
Narrow range in Sumif formula | Steve | Worksheet Functions | 2 | January 14th, 2004 06:16 PM |
SUMIF used in a link | Nat | Worksheet Functions | 1 | November 17th, 2003 09:02 PM |