Question about IF in formula

[bamaisgreat]

I have the following code in Column H.
Sometimes I have the letters "ID" entered in some of the cells in the F column. Of course when I do this I dont get a value in my cell in column H. Could someone help me adjust the formula so I can still get my value?

=IF(E151+[COLOR=#ff0000]F151[/COLOR]+G151,E151+[COLOR=#ff0000]F151[/COLOR]+G151,"")

Thanks

 

[Logit]

.
This is one method :

The formula in H151 :

=add_num(E151,F151,G151)

In a Routine Module, paste this macro :

Option Explicit


Function add_num(cell1, ParamArray Arr() As Variant)


    Dim temp As Double
    Dim i
    For i = LBound(Arr) To UBound(Arr)
        temp = temp + GetNumber(Arr(i))
    Next


    add_num = GetNumber(cell1.Value) + temp
End Function


Function GetNumber(ByVal str As String) As Double
    Dim objRegEx As Object
    Dim allMatches
    Dim i As Integer
    Dim result As Integer
    
    Set objRegEx = CreateObject("VBScript.RegExp")
    objRegEx.IgnoreCase = True
    objRegEx.Global = True


    objRegEx.Pattern = "\d{1,2}([\.,][\d{1,2}])?"


    Set allMatches = objRegEx.Execute(str)


    For i = 0 To allMatches.Count - 1
        result = result & allMatches.Item(i)
    Next


    GetNumber = result
End Function

 

[bamaisgreat]

ok thanks. I done that and it showed my value as NAME

 

[jtakw]

I have the following code in Column H.
Sometimes I have the letters "ID" entered in some of the cells in the F column. Of course when I do this I dont get a value in my cell in column H. Could someone help me adjust the formula so I can still get my value?

=IF(E151+[COLOR=#ff0000]F151[/COLOR]+G151,E151+[COLOR=#ff0000]F151[/COLOR]+G151,"")

Thanks

Hi,

I'm not getting what you want done, what exactly are you trying to accomplish with your formula above?
Are you trying SUM something, but Column F may or may not contain text values?
Can you give a few examples of your data and expected results?

 

[jtakw]

Hi,

Since you didn't reply when I posted above and you were on line at the time, I can only guess what you want, there may be a simpler way if you answered my questions above, but based on what I can gather, the following will do what you need, adjust cell references to suit:

Book1
	C	D	E	F	G
1	4		1	ID	3
2	5		TEXT	2	3
3			TEXT	ID	OTHER TEXT
4	6		2		4
Sheet1

Cell Formulas
Range	Formula
C1	=IF(SUM(E1:G1)>0,SUM(E1:G1),"")

C1 formula copied down.

 

[Logit]

.
Here is a link to download my workbook.

https://www.amazon.com/clouddrive/share/DPgHSroWPnFy5h5NEp07w2ulTXltvZl9QHFubR42DO0

 

[bamaisgreat]

so sorry for not replying back last night. All im really doing is adding up E, F & G151 but sometimes there may be some letters typed in the cells. Example E151 would have 25.00 F151 may have a number or could have the letters ID typed in and G151 had say 20.00 I would like the value in H151 to still show the sum of 45.00. This would copy down. Thanks for your help.
=IF(E151+F151+G151,E151+F151+G151,"")

 

[jtakw]

so sorry for not replying back last night. All im really doing is adding up E, F & G151 but sometimes there may be some letters typed in the cells. Example E151 would have 25.00 F151 may have a number or could have the letters ID typed in and G151 had say 20.00 I would like the value in H151 to still show the sum of 45.00. This would copy down. Thanks for your help.
=IF(E151+F151+G151,E151+F151+G151,"")

In that case, my formula in Post #5 will work, it will give you the SUM and ignore the TEXT, as long as the SUM is greater than 0, just put it in Column H and copy formula down.

Excel 2010
	E	F	G	H
1	1	ID	3	4
2	TEXT	2	3	5
3	TEXT	ID	OTHER TEXT
4	2		4	6
5	5	6	7	18
Sheet1

Cell Formulas
Range	Formula
H1	=IF(SUM(E1:G1)>0,SUM(E1:G1),"")

This is the exact formula as in my post #5 above.

 

[Logit]

.
The formula and Function provide the requested result.

Refer back to my Post #2 . Paste the Function code in a ROUTINE MODULE and the formula

[COLOR=#333333]=add_num(E151,F151,G151)[/COLOR]

in H151.
The desired result will appear in H151 when data is entered in the other three cells.

The example formula display in the attached workbook is set up for E1,F1,G1 with the answer in H1. That is only a demo ... just ignore it.

Using the attached workbook .... after downloading it ... take this formula

[COLOR=#333333]=add_num(E151,F151,G151)[/COLOR]

and paste in H151 .... enter data in E151,F151,G151 and the desired result will show in H151.