Sum and if statement

Some videos you may like

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
Joined
Aug 31, 2005
Messages
5,828
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?
 

puntwear

New Member
Joined
Dec 13, 2005
Messages
4
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
Joined
Feb 18, 2002
Messages
13,690

ADVERTISEMENT

Still not sure, but try

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

HTH

lenze
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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
Joined
Dec 13, 2005
Messages
4

ADVERTISEMENT

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
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,391
Messages
5,571,855
Members
412,421
Latest member
Rimo86
Top