Sumif question

[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?

 

[Ron Rosenfeld]

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.

 

[Don Guillett]

You can do this with various methods implemented through VBA, but not with the SUMIF function directly, as far as I know.

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

 

[Don Guillett]

You can do this with various methods implemented through VBA, but not with the SUMIF function directly, as far as I know.

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

 

[Don Guillett]

=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.

 

[Ron Rosenfeld]

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

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.

 

[Paul Hyett]

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.

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.

 

[Ron Rosenfeld]

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
========================

 

[Paul Hyett]

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
========================

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.

 

[Ron Rosenfeld]

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
========================

 

[Paul Hyett]

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
========================

Thank you - that does just what I want!

 

[Ron Rosenfeld]

Thank you - that does just what I want!

Glad to help. Thanks for the feedback.