if function help
 

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

if function help
 

Use SUMPRODUCT to handle multiple conditions...

=SUMPRODUCT(B1:B14*(B1:B14151)*(B1:B14<366))

--
Rick (MVP - Excel)



"kathy" wrote in message
...
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

if function help
 

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" wrote in message
...
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

if function help
 

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" wrote in message
...
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

if function help
 

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" wrote in message
...
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

if function help
 

You are welcome

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"kathy" wrote in message
...
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" wrote in message
...
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