# 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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

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

Thanks for your help!!

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

Thanks for your help!!

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

Replies
2
Views
56
Replies
21
Views
224
Replies
2
Views
53
Replies
2
Views
24
Replies
4
Views
47