acdecal
asked on
Sum cells that have no formula
Hello,
I have data in a ranges (rows) where the cells in the range contain a formula for forecasting. Users are to overwrite the formula when actual figures are known and I have conditional formatting to highlight the cell. I would like a formula to sum the actual numbers entered for each row and not include the cells with formulas. I tried defining a VBA function to test for cell color but it will not pick up conditional formatting changes.
Any help on this please?
I have data in a ranges (rows) where the cells in the range contain a formula for forecasting. Users are to overwrite the formula when actual figures are known and I have conditional formatting to highlight the cell. I would like a formula to sum the actual numbers entered for each row and not include the cells with formulas. I tried defining a VBA function to test for cell color but it will not pick up conditional formatting changes.
Any help on this please?
ASKER
Is there a non VBA function I can use in a SUMIF ?
I don't believe there is a built in one...
but thinking you could use define it like this in vba:
Then use =containsFormula in your worksheet or in your sumif function.
but thinking you could use define it like this in vba:
Function containsFormula(aCellRange As Range)
containsFormula = aCellRange.HasFormula
End FunctionThen use =containsFormula in your worksheet or in your sumif function.
ASKER
I tried =SUMIF(AB10:AQ10,ContainsF
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
change your formula to make the resulting figure text instead of a number.
=TEXT([formula],"#,###.00"
sum won't add text fields.
=TEXT([formula],"#,###.00"
sum won't add text fields.
ASKER
That did the trick! Thanks
I would suggest that you just test for the existence of a forumla...
Dim myCell as range
if myCell.HasFormula then....
or
if not myCell.HasFormula then....