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
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
=SUMPRODUCT(--(A3:A44={"M","W"}),K3:K44)
--
HTH
RP
(remove nothere from the email address if mailing direct)
"Chance224" <[email protected]> wrote in message
news:[email protected]...
> 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
>
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
----
"Chance224" <[email protected]> wrote in message
news:[email protected]...
> 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
>
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
>
>.
>
"Bob Phillips" <[email protected]> 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 <at>yahoo<dot>com
----
I think Bob meant this:
=3DSUMPRODUCT((A3:A44=3D{"M","W"})*K3:K44)
Jason
>-----Original Message-----
>"Bob Phillips" <[email protected]> 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 <at>yahoo<dot>com
>----
>
>
>.
>
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
"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
>
"Jason Morin" <[email protected]> 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 <at>yahoo<dot>com
----
=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" <[email protected]> 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 <at>yahoo<dot>com
>>----
>>
>>
>>.
>>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks