Please please please help me to count how many cells in a range have the SUM function

Andy
Try this UDF

Function GetSumF(MyRg As Range)
Application.Volatile
Dim oCell
Dim SumCount As Integer

For Each oCell In MyRg.Cells
If oCell.HasFormula Then
If InStr(1, oCell.Formula, "SUM", vbTextCompare) > 0 Then
SumCount = SumCount + 1
End If
End If
Next
GetSumF = SumCount
End Function

regards

Ivan

Here it is :-

Function countsum(theRange)
Application.Volatile
theRange.Replace What:="=sum", Replacement:="xsum", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
countsum = Application.WorksheetFunction.CountIf(theRange, “xSUM * ”)
theRange.Replace What:="xsum", Replacement:="=sum", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Function

I don’t think there is an Excel worksheet function that can check whether a cell contains a formula.
To count only the SUM formulas without using VBA, try this:-

1. Select A1:A8
2. Select Edit >Find >Replace >FindWhat:=SUM >ReplaceWith:xSUM >ReplaceAll
3. To count the cells, enter in an empty cell :- =COUNTIF(A1:A8,“xSUM*”).
4. To reinstate the formulas, Edit >Find >Replace >Find What:xSUM >ReplaceWith:=SUM >ReplaceAll

Please note that after step 4, the cell with the formula per step 3 will show the result "0".

Steps 1, 2 & 4 could be recorded in a macro and then the macro adjusted so that the result of step 3 could either be shown in a message box or put into a cell before step 4. For example, if the COUNTIF formula is put in B1:-

Sub Macro1()
Dim theRange As Range
Set theRange = Range("A1:A8")
theRange.Replace What:="=sum", Replacement:="xsum", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
MsgBox "There are " & Range("B1") & " cells with SUM formulas."
theRange.Replace What:="xsum", Replacement:="=sum", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub

Hope this helps
mads

Mads, God bless you, you made my day

It finally works with this slite addition:

Function countsum(theRange As Range) As Integer
Application.Volatile
y = 0
For Each cell In theRange
If cell.Formula Like "*SUM*" = True Then y = y + 1
Next
countsum = y
End Function

Thank you so much.

Thank you Mads. Actually I need that as a User Defined function.

Mads, I'm sorry to be a nuissance but when I used it, I get zero even if I have 3 cells with sum function in my range.

Mads please read this; it still drives me insane

Thanks so much Mads for your effort. I checked them out and is true it works when the range that you use in SUM function reference ranges in another column. As soon as you reference ranges in the same column where the SUM functions are, the result is decreasing by one until zero. Change the SUM functions like this and you'll see what I mean.
A1 1 NUMBER
A2 1 NUMBER
A3 2 =SUM(A1:A2)
A4 4 =SUM(A1:A3)
A5 1 NUMBER
A6 9 =SUM(A1:A5)
A7 1 NUMBER
A8 19 =SUM(A1:A7)
A9 1 NUMBER
A10 1 NUMBER

A12 0 =countsum(A1:A10)

It seems that's a tough one, cause I posted it in some other forums and no luck. Thank you again.

Mads, I think I figured out why it does not work. If in my range I have cells with SUM f it work. If I enter a number in any cell the macro returns zero (does not count anymore).

Mads, I tried it but I get an error message. I think we have to declare or set ws as something. I tried as object but I'm just guessing, it did not work. Thanks again.

Re: count cells with SUM function

I don't know why it doesn't work for you. It works for me.
mads

Thank you Ivan it works even better

I'm sorry Ivan, I have not noticed your post (expected new posts on the bottom of the thread). It works even better because makes distinction between function SUM and text SUM even if in my case would have been unlikely to have in the same column cells with function SUM and some verbage that will have the text =SUM. Thank you so much to you and Mads.

Creating a Custom Design F that counts cells with SUM function drives me nuts...HELP HELP HELP

Mads I do not know why but it does not work. Anyway I'm trying the following and it seems so close to work. If I count cells that have # 4 it counts properly but when I change to count SUM it counts all cells in range A1:A9. Maybe starting from here you can fix it. Thanks alot to you and anybody else who can help me with this.

Function CountSum2(myrange As Range) As Integer
Application.Volatile
Dim cell
For Each cell In myrange.Cells

On Error Resume Next
'If cell.Value = "4" Then
If cell.Formula = Application.WorksheetFunction.Sum(cell.Value) Then
CountSum2 = CountSum2 + 1
End If
Next
End Function

It works for me.
Have you copied and pasted the code from this board?
Don't know what else to suggest.
Try running it as a normal macro(not a function). If it works as a normal macro, then it should also work as a function !

mads

Yep, go for it !
mads

Re: function drives me nuts

What's your E-mail address? I'll send you a workbook containing the function that works.
Also, what version of Excel are you using?
mads

Try deleting "Option Explicit" that appears at the top of your module.
mads

Re: Mads, God bless you, you made my day

Or just enter the value of

2

Re: Mads please read this; it still drives me insane

Try this one :-

Function countsum(theRange)
Application.Volatile
y = 0
For Each cell In theRange
If cell.Formula Like "*SUM*" = True Then y = y + 1
Next
countsum = y
End Function

mads

Re: function drives me nuts

Thanks Mads. I'm using Office 97 on Windows NT and Office 98 for Mac (on both I have the same trouble). You can email me at User223344@excite.com. Thank you again for all your trouble.