Sum and if statement

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
What do you mean?

Do you want something like: =IF(A1="whatever",Sum(B1:B5),""), where the first argument checks a condition and if it is true, do the summing, else do something else, like leave cell blank?
 
Upvote 0
Sorry for lack of specifics...

I have a cell that has a sum statement in it, but I need to have it insert (or sum) a value (8) for a variety of text characters if one is indeed present.
In one sheet If the text is present, there will be no other numeric value in any of the cells included in the sum statement, in another sheet, I may have both numerics and text to consider.

Perhaps there is a formula to have the text value equal the value (8) instead of using the "If" statement?



Thanks for your help!!
 
Upvote 0
puntwear said:
Sorry for lack of specifics...

I have a cell that has a sum statement in it, but I need to have it insert (or sum) a value (8) for a variety of text characters if one is indeed present.
In one sheet If the text is present, there will be no other numeric value in any of the cells included in the sum statement, in another sheet, I may have both numerics and text to consider.

Perhaps there is a formula to have the text value equal the value (8) instead of using the "If" statement?



Thanks for your help!!

Is it a specific, known text value which must be valued as 8?
 
Upvote 0
Yes, this is for a timesheet. Any text entered would be representing 8 (hours).

lenze, you nailed it but I don't undertsand why what the "n" represents in your formula.
=SUM(A2:B2)+N(ISTEXT(D2)*8)

You patience to a newbie is appreciated.


Thanks,

Mike
 
Upvote 0
puntwear:

N() is a seldom used function tht returns certain types of values. It was created when problems arose between different speadsheet programs when recognizing transfered data. Basicaly, if the expression inside N(exp) is a number it returns the number. If the expression is a date, it returns the date. If the expression is text, it returns zero. For anything else it also returns zero. That said and done, it is, of course not really necessary. The formula above could be written

=SUM(A2:B2)+(ISTEXT(D2)*8)

I just like to use N() because it is SO neglected these days

lenze

Note: Aladin's formula should work fine and may be more efficient
 
Upvote 0
lenze said:
...
Basicaly, if the expression inside N(exp) is a number it returns the number. If the expression is a date, it returns the date. If the expression is text, it returns zero. For anything else it also returns zero.

...

It would coerce logical values into their numerical Excel equivalents:

=N(TRUE) ==> 1

=N(FALSE) ==> 0
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top