ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   need help with SUMIF (https://www.excelbanter.com/excel-worksheet-functions/106543-need-help-sumif.html)

Kerri Olsen
need help with SUMIF
 
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

Trevor Shuttleworth
need help with SUMIF
 
Kerri

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

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

Regards

Trevor


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



Bob Phillips
need help with SUMIF
 
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)

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

PCLIVE
need help with SUMIF
 
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)

"Trevor Shuttleworth" wrote in message
...
Kerri

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

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

Regards

Trevor


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

Kerri Olsen
need help with SUMIF
 
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
--
Kerri


"Bob Phillips" wrote:

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)

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

PCLIVE
need help with SUMIF
 
The formula provided should do that.
What are you getting?

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


"Bob Phillips" wrote:

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)

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

Bob Phillips
need help with SUMIF
 
That is exactly what I gave you!

--
HTH

Bob Phillips

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

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


"Bob Phillips" wrote:

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)

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

Trevor Shuttleworth
need help with SUMIF
 
I'm just gonna have to get that key fixed.

Thanks


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

"Trevor Shuttleworth" wrote in message
...
Kerri

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

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

Regards

Trevor


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

Kerri Olsen
need help with SUMIF
 
I am getting 0, when is should be 4.5
--
Kerri


"PCLIVE" wrote:

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

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


"Bob Phillips" wrote:

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)

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

Kerri Olsen
need help with SUMIF
 

--
Kerri


"Bob Phillips" wrote:

That is exactly what I gave you!

--
HTH

Bob Phillips

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

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


"Bob Phillips" wrote:

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)

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

Trevor Shuttleworth
need help with SUMIF
 
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


"Kerri Olsen" wrote in message
...
I am getting 0, when is should be 4.5
--
Kerri


"PCLIVE" wrote:

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

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


"Bob Phillips" wrote:

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)

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

Kerri Olsen
need help with SUMIF
 
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
--
Kerri


"Kerri Olsen" wrote:


--
Kerri


"Bob Phillips" wrote:

That is exactly what I gave you!

--
HTH

Bob Phillips

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

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


"Bob Phillips" wrote:

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)

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

Bob Phillips
need help with SUMIF
 
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)

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


"Kerri Olsen" wrote:


--
Kerri


"Bob Phillips" wrote:

That is exactly what I gave you!

--
HTH

Bob Phillips

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

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


"Bob Phillips" wrote:

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)

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


All times are GMT +1. The time now is 07:00 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com