Remember Me?

 Chance224 Posts: n/a 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

 Bob Phillips Posts: n/a =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

 Jason Morin Posts: n/a 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

.

 Max Posts: n/a 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

 Max Posts: n/a "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
----

 Jason Morin Posts: n/a 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
----

.

 John Britto Posts: n/a 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 =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:



=SUMPRODUCT((A3:A44="M")+(A3:A44="W"),K3:K44)

as Max suggested.

The following invokes an efficient idiom for or'ing...



=SUMPRODUCT(--ISNUMBER(MATCH(A3:A44,{"M","W"},0)),K3:K44)

An equivalent setup with SumIf is...



=SUMPRODUCT(SUMIF(A3:A44,{"M","W"},K3:K44))

where Sum can be sustituted for SumProduct when a constant array of

To recap, with J1:J2 housing the conditions "M" and "W"...

 SUMPRODUCT((A3:A44=J1)+(A3:A44=J2),K3:K44)
 SUMPRODUCT(--ISNUMBER(MATCH(A3:A44,J1:J2,0)),K3:K44)
 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
----

.

Posted to microsoft.public.excel.misc
 [email protected] external usenet poster Posts: 1 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<")
 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post ScottBerger Excel Worksheet Functions 2 April 23rd 23 09:05 PM Kellyatisl Excel Worksheet Functions 3 June 4th 06 06:33 AM vanjohnson Excel Discussion (Misc queries) 1 March 4th 05 08:42 PM Oscar Excel Worksheet Functions 2 January 11th 05 11:01 PM Elijah Excel Worksheet Functions 7 November 21st 04 02:17 PM

All times are GMT +1. The time now is 10:55 PM. Copyright ©2004-2023 ExcelBanter.