Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 203
  • Last Modified:

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?
0
acdecal
Asked:
acdecal
  • 3
  • 3
1 Solution
 
Ken ButtersCommented:
Since the cell is going to be updated to no longer contain a formula...

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....
0
 
acdecalAuthor Commented:
Is there a non VBA function I can use in a SUMIF ?
0
 
Ken ButtersCommented:
I don't believe there is a built in one...

but thinking you could use define it like this in vba:

Function containsFormula(aCellRange As Range) 
    containsFormula = aCellRange.HasFormula 
End Function

Open in new window


Then use =containsFormula in your worksheet or in your sumif function.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
acdecalAuthor Commented:
I tried =SUMIF(AB10:AQ10,ContainsFormula(AB10:AQ10))  but not working.  Any thoughts?
0
 
Ken ButtersCommented:
You are right sumif... doesn't like that for the criteria....

I tried this... this seemed to do what you are asking:

After adding this vba code... then in your sheet you can use it like this:

=sumIfNoFormula(AB10:AQ10)

it will include in the sum... any of the cells that do NOT contain a formula.

Function sumIfNoFormula(aCellRange As Range)
    Dim aCell As Range
    sumIfNoFormula = 0
    For Each aCell In aCellRange
        If Not aCell.HasFormula Then
            sumIfNoFormula = sumIfNoFormula + aCell.Value
        End If
    Next
End Function

Open in new window

0
 
rlarianCommented:
change your formula to make the resulting figure text instead of a number.
=TEXT([formula],"#,###.00")
sum won't add text fields.
0
 
acdecalAuthor Commented:
That did the trick!  Thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now