Sumif question

  • Thread starter Thread starter Paul Hyett
  • Start date Start date
P

Paul Hyett

Is there any way it can distinguish in a range, between values
calculated by a formula, and ones entered as values directly into a
cell?
 
You can do this with various methods implemented through VBA, but not with the SUMIF function directly, as far as I know.
Click to expand...

formula
=IF(LEFT(K6,1)="","formula","value")
or vba using hasformula
 
You can do this with various methods implemented through VBA, but not with the SUMIF function directly, as far as I know.
Click to expand...

formula
=IF(LEFT(K6,1)="","formula","value")
or vba using hasformula
 
=IF(LEFT(K6,1)="","formula","value")



You can do this with various methods implemented through VBA, but not with the SUMIF function directly, as far as I know.
Click to expand...
 
On my system, your formula returns "formula" if K6 is blank, otherwise
it returns value no matter whether K6 contains a constant or a formula.
Click to expand...

I assumed that there should have been an = within the empty quotes. Not
that it works even if you do so, though.

I don't suppose anyone could help with a vba solution to my original
question, though - i.e. summing just the cells in a range that contain
formulas? Or just those that contain values - either way, simple
subtraction could then provide the split I'm looking for.

TIA.
 
You can do this with a User Defined Function.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual
Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=CountFormulas(A1:A10)

in some cell.

========================================
Option Explicit
Function CountFormulas(rg As Range) As Double
Dim c As Range
Dim t As Double
For Each c In rg
If c.HasFormula Then t = t + 1
Next c
CountFormulas = t

End Function
========================
Click to expand...
Thanks.

Well that works, but it just counts the number of cells in the range
with formulas - it doesn't sum the contents of the formula cells, which
is what I was really looking for.
 
Simple change:

========================
Option Explicit
Function sumFormulas(rg As Range) As Double
Dim c As Range
Dim t As Double
For Each c In rg
If c.HasFormula Then t = t + c.Value
Next c
sumFormulas = t
End Function
========================
Click to expand...

Thank you - that does just what I want! :)
 
Back
Top