Applying Count formula to the range of an existing sum formula

H

harry2908

Board Regular
Joined
May 7, 2010
Messages
87
Hi,

There is a sum formula applied in column J of a sheet, which totals figures above it. I need another formula besides it which would count the number of items used in that sum formula.

Can someone suggest a formula or a macro for the same.

Thanks..
 
Sort by date Sort by votes

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Thanks Jeff..

I am able to get the range by applying the below formula, but the
=Count(GetFormulaRange(J213)) is not working, it gives the output as 0. whereas the total is done for J2:J211.

Any idea why this is..?

Thanks..
 
Upvote 0
Hello Harry2908,

Yes this is because the range is actually a string (words).

Use the INDIRECT() like:

<TABLE style="WIDTH: 285pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=380><COLGROUP><COL style="WIDTH: 285pt; mso-width-source: userset; mso-width-alt: 6948" width=380><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #d7e4bc; WIDTH: 285pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=380>=COUNT(INDIRECT(GetFormulaRange(A1)))</TD></TR></TBODY></TABLE>

Also this UDF would not work with formulas with more parenthesis than a simple =sum(a1:b1). It finds the first, crops from there to the end, then clips the last paren to arrive at the answer.

-Jeff
 
Upvote 0
Thanks Jeff..

This is perfetly working on a simple formula but no working where a few cells are selected to get the total.

so Jeff do you mean that this wont work on the formula as
=SUM(J42:J45,J20:J39,J12:J18,J9:J10,J5:J7,J2)?
 
Upvote 0
I have tried several attemps to make that work. Seems INDIRECT() is limited to single ranges and does not play nice with more than one group at a time.

I do hope you can use the following UDF. This is a spin off of the previous request... takes it another step and counts for you. In other words, get the range(s) and returns a count of cells with numbers.

Again note this will only work with formulas with only one pair of parens like:

=sum(A1:A2,A3:A4,A5:A6)

<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> GFRandCOUNT(theFormula <SPAN style="color:#00007F">As</SPAN> Range) <SPAN style="color:#007F00">'''Get Formula Range and Count Cells</SPAN><br><br><SPAN style="color:#00007F">Dim</SPAN> FFR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>   <SPAN style="color:#007F00">'''Formula From Range</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> FindParen<br><SPAN style="color:#00007F">Dim</SPAN> CellCount <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> BlankCell <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><br>    FFR = theFormula.Formula<br>    <br>    FindParen = Application.WorksheetFunction.Find("(", FFR)<br>    <br>    FFR = Mid(FFR, FindParen + 1, Len(FFR) - FindParen - 1)<br>    <br>    <SPAN style="color:#007F00">'Get<SPAN style="color:#00007F">For</SPAN>mulaRange = FFR</SPAN><br>    <br>    For <SPAN style="color:#00007F">Each</SPAN> x <SPAN style="color:#00007F">In</SPAN> Range(FFR)<br>           <SPAN style="color:#00007F">If</SPAN> IsEmpty(x.Value) <SPAN style="color:#00007F">Then</SPAN> BlankCell = BlankCell + 1<br>    <SPAN style="color:#00007F">Next</SPAN> x<br>    <br>    CellCount = Range(FFR).Cells.Count<br>    <br>    GFRandCOUNT = CellCount - BlankCell<br>    <br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br></FONT>

-Jeff
 
Upvote 0
Hi Jeff,

Sorry to be a pain. Can the macro also color the font color white of the cell where Count formula is applied?

Please find the actual macro below, can you please make the necessary changes.

Appreciate your help..

Code: 
Sub d_CellColor_and_Total_in_all_sheets_COUNT()
Dim i As Long, j As Long
Dim k As Integer, l As Integer
Dim LastRow As Long
Dim ws As Object
For Each ws In Worksheets
ws.Select
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("B1:B" & LastRow).Select
Selection.Sort Key1:=Range("B1"), Order1:=xlDescending, HEADER:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
For i = LastRow To 1 Step -1
If Range("B" & i).Value >= 0 And Range("B" & i).Value <= 9 Then Range(("A" & i), Range("P" & i)).Interior.ColorIndex = 36
If Range("B" & i).Value >= 10 And Range("B" & i).Value <= 14 Then Range(("A" & i), Range("P" & i)).Interior.ColorIndex = 4
If Range("B" & i).Value >= 15 And Range("B" & i).Value < 20 Then Range(("A" & i), Range("P" & i)).Interior.ColorIndex = 44
If Range("B" & i).Value >= 20 And Range("B" & i).Value < 100 Then Range(("A" & i), Range("P" & i)).Interior.ColorIndex = 3
Range("B1:B" & LastRow).Select
Selection.Sort Key1:=Range("B1"), Order1:=xlDescending, HEADER:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Next i
k = 3
For j = LastRow To 1 Step -1
If Range("B" & j).Interior.Color <> Range("B" & j + 1).Interior.Color Then
Range("B" & j + 1).Select
For l = 1 To k
    Selection.EntireRow.Insert
    Selection.EntireRow.Clear
    
Next l
End If
Next j
    Range("2:4").Select
    Selection.Delete Shift:=xlToup
    
    Dim LR As Long
    Dim Area As Range
    With ActiveSheet
        LR = .Range("J" & .Rows.Count).End(xlUp).Row
        If LR = 2 Then
            .Range("J4").Formula = "=SUM(J2)"
        Else
            For Each Area In .Range("J2:J" & LR).SpecialCells(xlCellTypeConstants).Areas
                With Area.Resize(1).Offset(Area.Rows.Count + 1)
                    .Formula = "=SUM(" & Area.Address & ")"
                    .Offset(0, 1).Formula = "=COUNT(" & Area.Address & ")"
                
                End With
            Next Area
        End If
    End With
Next
End Sub
 
Upvote 0
See bottom of message
harry2908 said:
Hi Jeff,

Sorry to be a pain. Can the macro also color the font color white of the cell where Count formula is applied?

Please find the actual macro below, can you please make the necessary changes.

Appreciate your help..

Code: 
Sub d_CellColor_and_Total_in_all_sheets_COUNT()
Dim i As Long, j As Long
Dim k As Integer, l As Integer
Dim LastRow As Long
Dim ws As Object
For Each ws In Worksheets
ws.Select
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("B1:B" & LastRow).Select
Selection.Sort Key1:=Range("B1"), Order1:=xlDescending, HEADER:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
For i = LastRow To 1 Step -1
If Range("B" & i).Value >= 0 And Range("B" & i).Value <= 9 Then Range(("A" & i), Range("P" & i)).Interior.ColorIndex = 36
If Range("B" & i).Value >= 10 And Range("B" & i).Value <= 14 Then Range(("A" & i), Range("P" & i)).Interior.ColorIndex = 4
If Range("B" & i).Value >= 15 And Range("B" & i).Value < 20 Then Range(("A" & i), Range("P" & i)).Interior.ColorIndex = 44
If Range("B" & i).Value >= 20 And Range("B" & i).Value < 100 Then Range(("A" & i), Range("P" & i)).Interior.ColorIndex = 3
Range("B1:B" & LastRow).Select
Selection.Sort Key1:=Range("B1"), Order1:=xlDescending, HEADER:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Next i
k = 3
For j = LastRow To 1 Step -1
If Range("B" & j).Interior.Color <> Range("B" & j + 1).Interior.Color Then
Range("B" & j + 1).Select
For l = 1 To k
    Selection.EntireRow.Insert
    Selection.EntireRow.Clear
    
Next l
End If
Next j
    Range("2:4").Select
    Selection.Delete Shift:=xlToup
    
    Dim LR As Long
    Dim Area As Range
    With ActiveSheet
        LR = .Range("J" & .Rows.Count).End(xlUp).Row
        If LR = 2 Then
            .Range("J4").Formula = "=SUM(J2)"
        Else
            For Each Area In .Range("J2:J" & LR).SpecialCells(xlCellTypeConstants).Areas
                With Area.Resize(1).Offset(Area.Rows.Count + 1)
                    .Formula = "=SUM(" & Area.Address & ")"
                    .Offset(0, 1).Formula = "=COUNT(" & Area.Address & ")"
                    [B][COLOR=Red].Offset(0, 1).Font.ColorIndex = 2[/COLOR][/B]
                End With
            Next Area
        End If
    End With
Next
End Sub
Click to expand...
Add the line in red above.
 
Upvote 0
Hi,

The above mentioned macro dosen't do the count if there is only one line in the spreadsheet. Can someone kindly update the same so that it does the count where there is one line item.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
COUNT multiple SUM COUNTIFS
Replies
6
Views
337
Peter_SSs
Peter_SSs
E
  • Solved
changing (and understand) the inside of a formula with a macro
Replies
5
Views
195
myall_blues
myall_blues
P
  • Solved
Counting number of cells with formula returning a value OR a #DIV/0 error?
Replies
3
Views
209
Eric W
Eric W
B
  • Solved
how to use find formula in a countif formula
Replies
5
Views
55
etaf
etaf
M
  • Solved
vba to sum up each column for each employee on column (A)
Replies
6
Views
330
kvsrinivasamurthy
K

Forum statistics

Threads
1,224,583
Messages
6,179,673
Members
452,937
Latest member
Bhg1984

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top