Sum and if statement

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
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,210
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,210
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
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
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
Top