if function help
in [Functions]
|
Prev: How do I delete a calendar function from a spreadsheet
Next: How can I merge unprotected cells in a protected worksheet?
From: kathy on 27 Apr 2010 17:40 i am trying to create an if function that will sum data in a column if the cell value is between certain values. This works for sum if greater than, =ifsum(b:4:b14,"<366",e4:E14) When I try to have the sum of E4:e14 if b4:b14 is between 151 and 366 I am unable to get the criteria written correctly. HELP! -- Thank you, Kathy
From: Rick Rothstein on 27 Apr 2010 18:24 Use SUMPRODUCT to handle multiple conditions... =SUMPRODUCT(B1:B14*(B1:B14>151)*(B1:B14<366)) -- Rick (MVP - Excel) "kathy" <kathy(a)discussions.microsoft.com> wrote in message news:A9F1BC40-F9DD-42F5-96BD-3618D128F723(a)microsoft.com... > i am trying to create an if function that will sum data in a column if the > cell value is between certain values. > This works for sum if greater than, =ifsum(b:4:b14,"<366",e4:E14) > When I try to have the sum of E4:e14 if b4:b14 is between 151 and 366 I am > unable to get the criteria written correctly. HELP! > -- > Thank you, Kathy
From: Ashish Mathur on 28 Apr 2010 02:25 Hi, Try this =sumif(B4:B14,">=151",E4:E14)-sumif(B4:B14,">366",E4:E14) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "kathy" <kathy(a)discussions.microsoft.com> wrote in message news:A9F1BC40-F9DD-42F5-96BD-3618D128F723(a)microsoft.com... > i am trying to create an if function that will sum data in a column if the > cell value is between certain values. > This works for sum if greater than, =ifsum(b:4:b14,"<366",e4:E14) > When I try to have the sum of E4:e14 if b4:b14 is between 151 and 366 I am > unable to get the criteria written correctly. HELP! > -- > Thank you, Kathy
From: kathy on 28 Apr 2010 09:33 Thank you that works, now how do I change this to count if with the same information. I tried changing sumif to countif, but the answer was not correct. -- Thank you, Kathy "Ashish Mathur" wrote: > Hi, > > Try this > > =sumif(B4:B14,">=151",E4:E14)-sumif(B4:B14,">366",E4:E14) > > -- > Regards, > > Ashish Mathur > Microsoft Excel MVP > www.ashishmathur.com > > "kathy" <kathy(a)discussions.microsoft.com> wrote in message > news:A9F1BC40-F9DD-42F5-96BD-3618D128F723(a)microsoft.com... > > i am trying to create an if function that will sum data in a column if the > > cell value is between certain values. > > This works for sum if greater than, =ifsum(b:4:b14,"<366",e4:E14) > > When I try to have the sum of E4:e14 if b4:b14 is between 151 and 366 I am > > unable to get the criteria written correctly. HELP! > > -- > > Thank you, Kathy >
From: kathy on 28 Apr 2010 09:36
Sorry for the last post, I figured it out, I had to remove the E4:e14 and now it works. -- Thank you, Kathy "Ashish Mathur" wrote: > Hi, > > Try this > > =sumif(B4:B14,">=151",E4:E14)-sumif(B4:B14,">366",E4:E14) > > -- > Regards, > > Ashish Mathur > Microsoft Excel MVP > www.ashishmathur.com > > "kathy" <kathy(a)discussions.microsoft.com> wrote in message > news:A9F1BC40-F9DD-42F5-96BD-3618D128F723(a)microsoft.com... > > i am trying to create an if function that will sum data in a column if the > > cell value is between certain values. > > This works for sum if greater than, =ifsum(b:4:b14,"<366",e4:E14) > > When I try to have the sum of E4:e14 if b4:b14 is between 151 and 366 I am > > unable to get the criteria written correctly. HELP! > > -- > > Thank you, Kathy > |