need help with SUMIF

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Kerri

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

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

Regards

Trevor
 
Kerri

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

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

Regards

Trevor
 
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)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
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)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
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)
 
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)
 
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
 
That is exactly what I gave you!

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
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
 
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
 
Sounds like it. Could it be the numbers are formatted as text?

--
HTH

Bob Phillips

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copying a formula and changing ONE value... 2
Formula 6
Sumifs, averageifs, countifs 6
nested function 4
Countif over a range of cells for various codes. 1
=countif(with two set of ranges; ">0") 2
average for year 1
Data Validation with Blank Date 2

Back
Top