# Sum and if statement

#### puntwear

##### New Member
How can I add an if statement in a cell that already has a sum formula?

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

#### NBVC

##### Well-known Member
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?

#### lenze

##### Legend
Welcome to the board

I'm not sure what you mean. can you be more specific? What formula is in the cell, and what do you want to do. In other words, what is the expected result? If you haven't done so, I also suggest you read the posting guidelines at http://www.mrexcel.com/board2/viewtopic.php?t=131687.

lenze

#### puntwear

##### New Member
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?

#### lenze

##### Legend

Still not sure, but try

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

HTH

lenze

##### MrExcel MVP
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?

Is it a specific, known text value which must be valued as 8?

#### puntwear

##### New Member

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

##### MrExcel MVP
puntwear said:
Yes, this is for a timesheet. Any text entered would be representing 8 (hours)...

=SUM(Range)+COUNTIF(Range,"?*")*8

#### lenze

##### Legend
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

##### MrExcel MVP
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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,006
Messages
5,834,835
Members
430,324
Latest member
bosphoruskid

### 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.

### Which adblocker are you using?

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

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