need help with SUMIF

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kerri Olsen - 22 Aug 2006 20:00 GMT

I need help writing a formula

if range E18:E23 is greater than 40, I want the number E18:E23-40 entered
into the cell, if false 0

Signature

Kerri

Reply to this Message

Trevor Shuttleworth - 22 Aug 2006 20:10 GMT

Kerri

not sure I understand what you want but, maybe this:

=IF(SUM(E18:E23)>0,SUM(E18:E23)-40,0)

Regards

Trevor

>I need help writing a formula
>
> if range E18:E23 is greater than 40, I want the number E18:E23-40 entered
> into the cell, if false 0

Reply to this Message

PCLIVE - 22 Aug 2006 20:20 GMT

I think the formula was correct with the exception of the greater than
number. This should be it.

=IF(SUM(E18:E23)>40,SUM(E18:E23)-40,0)

> Kerri
>
[quoted text clipped - 10 lines]
>> if range E18:E23 is greater than 40, I want the number E18:E23-40 entered
>> into the cell, if false 0

Reply to this Message

Trevor Shuttleworth - 22 Aug 2006 22:42 GMT

I'm just gonna have to get that key fixed.

Thanks

>I think the formula was correct with the exception of the greater than
>number. This should be it.
[quoted text clipped - 16 lines]
>>> entered
>>> into the cell, if false 0

Reply to this Message

Bob Phillips - 22 Aug 2006 20:17 GMT

I think Trevor's should be

=IF(SUM(E18:E23)>40,SUM(E18:E23)-40,0)

and here is an alternative

=MAX(SUM(E18:E23)-40,0)

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> I need help writing a formula
>
> if range E18:E23 is greater than 40, I want the number E18:E23-40 entered
> into the cell, if false 0

Reply to this Message

Kerri Olsen - 22 Aug 2006 21:55 GMT

I guess my question is not clear

if sum of E18:E23 is over 40, I want the difference entered in the cell,
example total is 46 I want 6 in cell
if sum of E18:E23 is 40 or less, I want 0

Signature

Kerri

> I think Trevor's should be
>
[quoted text clipped - 8 lines]
> > if range E18:E23 is greater than 40, I want the number E18:E23-40 entered
> > into the cell, if false 0

Reply to this Message

PCLIVE - 22 Aug 2006 22:02 GMT

The formula provided should do that.
What are you getting?

>I guess my question is not clear
>
[quoted text clipped - 15 lines]
>> > entered
>> > into the cell, if false 0

Reply to this Message

Kerri Olsen - 22 Aug 2006 22:46 GMT

I am getting 0, when is should be 4.5

Signature

Kerri

> The formula provided should do that.
> What are you getting?
[quoted text clipped - 18 lines]
> >> > entered
> >> > into the cell, if false 0

Reply to this Message

Trevor Shuttleworth - 22 Aug 2006 22:58 GMT

I'd check your data. Maybe you've got text that looks like a number and
isn't getting added in. Maybe one that looks like 4.5 ? Could be a space
in there somewhere ?

Regards

Trevor

>I am getting 0, when is should be 4.5
>
[quoted text clipped - 21 lines]
>> >> > entered
>> >> > into the cell, if false 0

Reply to this Message

Bob Phillips - 22 Aug 2006 22:36 GMT

That is exactly what I gave you!

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> I guess my question is not clear
>
[quoted text clipped - 14 lines]
> > > if range E18:E23 is greater than 40, I want the number E18:E23-40 entered
> > > into the cell, if false 0

Reply to this Message

Kerri Olsen - 22 Aug 2006 22:52 GMT

Signature

Kerri

> That is exactly what I gave you!
>
[quoted text clipped - 17 lines]
> entered
> > > > into the cell, if false 0

Reply to this Message

Kerri Olsen - 22 Aug 2006 23:03 GMT

Thank you, it worked in another part of my work sheet, not where I want it
to, could be the formating of some of the data.

Thank you

Signature

Kerri

> > That is exactly what I gave you!
> >
[quoted text clipped - 17 lines]
> > entered
> > > > > into the cell, if false 0

Reply to this Message

Bob Phillips - 23 Aug 2006 09:20 GMT

Sounds like it. Could it be the numbers are formatted as text?

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Thank you, it worked in another part of my work sheet, not where I want it
> to, could be the formating of some of the data.
[quoted text clipped - 22 lines]
> > > entered
> > > > > > into the cell, if false 0

Reply to this Message