Discussion:
SUMIF HELP
(too old to reply)
Chance224
2005-03-15 16:49:04 UTC
Permalink
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
Bob Phillips
2005-03-15 16:55:27 UTC
Permalink
=SUMPRODUCT(--(A3:A44={"M","W"}),K3:K44)
--
HTH

RP
(remove nothere from the email address if mailing direct)
Post by Chance224
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
2005-03-15 17:25:18 UTC
Permalink
Post by Bob Phillips
=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 <at>yahoo<dot>com
----
Jason Morin
2005-03-15 18:47:19 UTC
Permalink
I think Bob meant this:

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

Jason
-----Original Message-----
Post by Bob Phillips
=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 <at>yahoo<dot>com
----
.
Max
2005-03-15 23:10:35 UTC
Permalink
Post by Jason Morin
=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 <at>yahoo<dot>com
----
Aladin Akyurek
2005-04-03 03:44:45 UTC
Permalink
=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.
Post by Jason Morin
=SUMPRODUCT((A3:A44={"M","W"})*K3:K44)
Jason
-----Original Message-----
Post by Bob Phillips
=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 <at>yahoo<dot>com
----
.
s***@gmail.com
2016-09-22 07:40:06 UTC
Permalink
Post by Bob Phillips
=SUMPRODUCT(--(A3:A44={"M","W"}),K3:K44)
--
HTH
RP
(remove nothere from the email address if mailing direct)
Post by Chance224
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<")

Max
2005-03-15 16:59:12 UTC
Permalink
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 <at>yahoo<dot>com
----
Post by Chance224
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
2005-03-15 16:58:49 UTC
Permalink
Using SUMPRODUCT offers the most flexibility, but in this
case you could use:

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

HTH
Jason
Atlanta, GA
-----Original 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â?Tm unsure of how to get it to do
two.
Thanks,
Chance
.
John Britto
2005-03-15 20:47:04 UTC
Permalink
Kindly use the formula in the following manner.

=SUMIF(A3:A6,"m",K3:K6)+SUMIF(A3:A6,"w",K3:K6)+SUMIF(A3:A6,"j",K3:K6)

Good luck..john britto
Post by Chance224
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
Loading...