#1   Report Post  
Chance224
 
Posts: n/a
Default 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   Report Post  
Bob Phillips
 
Posts: n/a
Default

=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   Report Post  
Jason Morin
 
Posts: n/a
Default

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

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

"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   Report Post  
Jason Morin
 
Posts: n/a
Default

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   Report Post  
John Britto
 
Posts: n/a
Default

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   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

=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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to use SUMIF to return sums between two values located in cells ScottBerger Excel Worksheet Functions 2 April 23rd 23 09:05 PM
How do I use the sumif if I have multiple criteria (i.e. greater . Kellyatisl Excel Worksheet Functions 3 June 4th 06 06:33 AM
SUMIF Not vanjohnson Excel Discussion (Misc queries) 1 March 4th 05 09:42 PM
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function Oscar Excel Worksheet Functions 2 January 12th 05 12:01 AM
SUM(IF( Array to avoid #NUM! values Elijah Excel Worksheet Functions 7 November 21st 04 03:17 PM


All times are GMT +1. The time now is 01:41 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"