SUM fuction to ignore text .

[XeenGR]

Hello everyone , i'm new to the forum and i hope you could me some advice . Also i like to mention that i'm new also to the excel .
I try to make a worksheet that counts some cells with different values . But i need sometimes to add text on the master cell but with the sum function ill get a Value error.. till now i couldn't find a way around . Would nice if some of you could help me out of this situation . The formula is =SUM(K148*5)+(L148*5)+((M148*5)*1.2)+((N148*5)). The only cell that may have not a number but a text in this example is the K148. I hope you understand my problem and i'm sorry about my english since its not my native language .

 

[aghaffar82]

Hello, Welcome to the Forum, try this:
=SUM(IFERROR(K148*5,0)+(IFERROR(L148*5,0)+((IFERROR(M148*5,0)*1.2)+((IFERROR(N148*5,0))))))

 

[Peter_SSs]

Welcome to the MrExcel board!

With cell K148, if it contains some text are you
a) trying to ignore that cell altogether, or
b) trying to ignore the text in the cell but still use any number also in the cell?

If a), try this formula instead

=SUM(K148:N148)*5+M148

If b) can you give us some examples of the sorts of things that might be in cell K148?

 

[XeenGR]

Hello, Welcome to the Forum, try this:
=SUM(IFERROR(K148*5,0)+(IFERROR(L148*5,0)+((IFERROR(M148*5,0)*1.2)+((IFERROR(N148*5,0))))))

Thank you , if worked like a charm !!!
Gonna recommend this forum for the help and friendly members !
Thank you agan .

 

[aghaffar82]

Thank you , if worked like a charm !!!
Gonna recommend this forum for the help and friendly members !
Thank you agan .

That's great, thank you for the feedback. Cheers!

 

[XeenGR]

And since we are working on that formula , is there any way in the same formula to add a second IF to check if a text let's say "Hello" give you back a fixed amount of number ? What i mean is , after the solution that Mr.aghaffa82 gave to be the formula something like

=SUM(IFERROR(K148*5,0)+(IFERROR(L148*5,0)+((IFERROR(M148*5,0)*1.2)+((IFERROR(N148*5,0)))))) , =IF(K148="HELLO" , 40,0

?
Sorry for continuing the thread .

 

[aghaffar82]

And since we are working on that formula , is there any way in the same formula to add a second IF to check if a text let's say "Hello" give you back a fixed amount of number ? What i mean is , after the solution that Mr.aghaffa82 gave to be the formula something like

=SUM(IFERROR(K148*5,0)+(IFERROR(L148*5,0)+((IFERROR(M148*5,0)*1.2)+((IFERROR(N148*5,0)))))) , =IF(K148="HELLO" , 40,0

?
Sorry for continuing the thread .

Hello, try this:
=SUM(IF(K148="Hello",40,0),IFERROR(K148*5,0)+(IFERROR(L148*5,0)+((IFERROR(M148*5,0)*1.2)+((IFERROR(N148*5,0))))))

I am not sure if you want to ignore the other result or want to add the value assigned to the text, the above formula adds 40 if cell K148 has the target text.

 

[XeenGR]

Hello, try this:
=SUM(IF(K148="Hello",40,0),IFERROR(K148*5,0)+(IFERROR(L148*5,0)+((IFERROR(M148*5,0)*1.2)+((IFERROR(N148*5,0))))))

I am not sure if you want to ignore the other result or want to add the value assigned to the text, the above formula adds 40 if cell K148 has the target text.

Exactly what i need . Thank you again !

 

[aghaffar82]

You're welcome dear

 

[Dave Patton]

try =(K148="Hello")*40+SUM(K148:N148)*5+SUM(M148)