Count and Sum Simultaneously ( even in filtered rows)

[a_dani20]

hi guys..

i am using excel 2003. Every time i select a range containing numbers i have to manually change sum status in status bar to count. i was looking for a vba function that could tell me the sum , count and average of numbers simultaneously. The vba function should also work on filtered rows.

Also i was wondering that is there is any way by which after selecting a range a small pop up ( just like which appears on selecting a range telling row and column number) appears automatically which tells the count and sum of selected range???

thanks in advance and sorry for that incomplete post ..

 

[Luke M]

Umm...can you elaborate on your question? Wild guess:

="My count is:"&COUNT(A:A)&" and my sum is:"&SUM(A:A)

 

[Elesario]

Please could you describe what you are trying to do?


The functions COUNT, COUNTA, COUNTIF, SUM, SUMIF, SUMPRODUCT are all unaffected by filters and can in various configurations used to be count or sum ranges of data. I'm not sure of what result is wanted that is a count and sum at the same time (unless you want an AVERAGE?)

 

[jeremymjp]

In Excel 2010 (sorry, don't have an '03 copy anymore), you can right-click the status bar and choose which metrics you'd like to see. In 2010, I get about 6 different options, including the three you're looking for.


Good luck.

 

[Elesario]

In Excel 2003 it'll show you metrics of your selected range which you can change from count to sums to min, max, average, etc... but only one figure at a time, whereas 2007/10 shows you several metrics in one go.


If this is a table or report in which you want a quick summary of the data that responds to filters then there's a function for that 'SUBTOTAL'.


=SUBTOTAL(Function_num, Range1[, Range2,...])


Function_num = Function

1 = AVERAGE

2 = COUNT

3 = COUNTA

4 = MAX

5 = MIN

6 = PRODUCT

7 = STDEV

8 = STDEVP

9 = SUM

10 = VAR

11 = VARP

NB: Add 100 to the function num to make it ignore hidden rows also (2003 and above)


The way I prefer to use it is if you have a table of data put a row of suitable subtotals 2 rows above the headers that have filters, so when the user applies a filter they see the change in results instantly.


Other than this to get extra information relating to the selected range you could write a macro that responds to the 'SheetSelectionChange' event which updates the 'Application.StatusBar' property with a string listing all the extra information you want, but this might cause you to lose the standard status text. For the extra mile it could be an addon with a non-modal dialog showing the extra info.

 

[magbo]

try this code:

(needs to be pasted in worsheet code)

[pre]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Cells.COUNT < 0 Then
Call StatusBar_Show
Else
Call StatusBar_Clear
End If
End Sub

Private Sub Worksheet_Deactivate()
Call StatusBar_Clear
End Sub

Sub StatusBar_Show()
Dim SUM As Single
Dim AVERAGE As Single
Dim COUNT As Single

SUM = Application.WorksheetFunction.Subtotal(9, Selection)
If SUM <> 0 Then AVERAGE = Application.WorksheetFunction.Subtotal(1, Selection) Else AVERAGE = 0
COUNT = Application.WorksheetFunction.Subtotal(2, Selection)

Application.StatusBar = "SUM = " & SUM & "   COUNT = " & COUNT & "   AVERAGE = " & AVERAGE
End Sub
Sub StatusBar_Clear()
Application.StatusBar = False
End Sub

Edit:

if you want to count only visible cells (excluding these manually hidden) use

SUM = Application.WorksheetFunction.Subtotal(109, Selection)
If SUM <> 0 Then AVERAGE = Application.WorksheetFunction.Subtotal(101, Selection) Else AVERAGE = 0
COUNT = Application.WorksheetFunction.Subtotal(102, Selection)

[/pre]

 

[a_dani20]

Magbo this is exactly what i wanted .. thanks a ton to you man.. by the way there is one small problem, in sum status it sometimes shows figure like this "2.678873E + 07" instead of proper number format. can u fix that?? and it would be great help if u can tell me procedure to incorporate this code in my custom menu such that everytime i open a new workbook it automatically appears in that menu..

Thanx

 

[asa]

Hi a_dani,

For the original version that calculates the entire selection, this change should work:

[pre]

If SUM <> 0 Then AVERAGE = Round(Application.WorksheetFunction.Subtotal(1, Selection),8) Else AVERAGE = 0
Similarly, for the second version that only considers visible cells, try this:

If SUM <> 0 Then AVERAGE = Round(Application.WorksheetFunction.Subtotal(101, Selection),8) Else AVERAGE = 0

[/pre]
Asa

 

[magbo]

As I don't know which number format is proper to you, here is the code which displays sum & average in format of active cell:

[pre]

Sub StatusBar_Show()
Dim SUM As Single
Dim AVERAGE As Single
Dim COUNT As Single

Dim SUM1 As String
Dim AVERAGE1 As String
Dim Format As String

Format = Application.ActiveCell.NumberFormat

SUM = Application.WorksheetFunction.Subtotal(109, Selection)
If SUM <> 0 Then AVERAGE = Application.WorksheetFunction.Subtotal(101, Selection) Else AVERAGE = 0
COUNT = Application.WorksheetFunction.Subtotal(102, Selection)

SUM1 = Application.WorksheetFunction.Text(SUM, Format)
AVERAGE1 = Application.WorksheetFunction.Text(AVERAGE, Format)
Application.StatusBar = "SUM = " & SUM1 & "   COUNT = " & COUNT & "   AVERAGE = " & AVERAGE1
End Sub

[/pre]

btw. "figure like this "2.678873E + 07" " - is simply scientific number format, which Excel uses for very big or very small numbers, especially when number is too long to fit in the column.