Macros for counting and sum

baigm · Oct 27, 2009

HI All,

I have the below data which i need a macro to be created, can any one help me on this!!!

Sl.No. Name Type Amount
1 A I 50
2 b II 60
3. C I 10
4. D II 20

this data will be in sheet one, wat i want the macros to do is it should count only the type I (like above Type one total count is 2 and total amount is 60) so as for Type II (count 2 total amount 80) and it should reflect these count in sheet 2 column count and total amount column.

Further, if i provide an command button to an macro how i can name that button so that it wont be confusing with other command buttons.

Please help on this

Thanks in advance.

Baig.

Legacy 14611 · Oct 27, 2009

You can do that with a macro, but why not a pivot table?

baigm · Oct 27, 2009

Hi i can,

But wat data i have given is just one part of the report, i need to consolidate all the data in single report.

and the total count and total sum of each type should be in one part of the cell in the table which i create manually.

Could you help in this.

Thanks.

Baig.

Legacy 14611 · Oct 27, 2009

Regarding your counting and summing, try this. I didn't really know what you want done with the results, but they can be very easily put on Sheet2.

Code:

Sub baig()
Dim a, n As Long, m As Integer, c()
Dim p As Integer, i As Long
a = Sheets("Sheet1").Cells(1).CurrentRegion
n = UBound(a, 1): m = UBound(a, 2)
ReDim c(1 To n, 1 To m)
With CreateObject("Scripting.Dictionary")
    For i = 2 To n
        If Not .exists(a(i, 3)) Then
            p = p + 1
            .Add a(i, 3), p
            c(p, 1) = a(i, 3)
            c(p, 2) = 1
            c(p, 3) = a(i, 4)
        Else
            c(.Item(a(i, 3)), 2) = c(.Item(a(i, 3)), 2) + 1
            c(.Item(a(i, 3)), 3) = c(.Item(a(i, 3)), 3) + a(i, 4)
        End If
    Next i
Cells(2, 6).Resize(p, 3) = c
Cells(1, 6) = "Type": Cells(1, 7) = "Count": Cells(1, 8) = "Sum"
End With
End Sub

Re your command button, right click on it and this gives the option of assigning to any macro you like, also gives the formatting option by which you can name it whatever you like, colorit, change font etc.

baigm · Oct 28, 2009

Hi rugila,

Thank u very much for the macros,

i have checked the same, when i try to run that it is displaying the count and sum in the same sheet, other columns F, G and H. can that data will be in last columns like column IM or IN etc.,

and also could you please tell me, if the types increases how can i add tat (eg. if type is III, IV, V, etc.,)

and as of command button, i have one more question, i have a created a pivot table using macros, and i hidden the other sheet except the pivot table. how can i add the command button in same work sheet and if i run the macros it should only shows the pivot table. (i have the data in Sheet one which i used for pivot)

Thanks again.

Baig.

Legacy 14611 · Oct 28, 2009

Baig

that macro will easily handle as many types as you like. include them in your list just as you have the others

if you want the count and sum results to start at the top of column IM instead of column F, then change the second and third to last lines of the code to

Code:

Cells(2, "IM").Resize(p, 3) = c
Cells(1, "IM") = "Type": Cells(1, "IN") = "Count": Cells(1, "IO") = "Sum"

Macros for counting and sum

baigm

Board Regular

Excel Facts

Legacy 14611

Guest

baigm

Board Regular

Legacy 14611

Guest

baigm

Board Regular

Legacy 14611

Guest

Similar threads

Forum statistics

Share this page

Macros for counting and sum

baigm

Board Regular

Excel Facts

Legacy 14611

Guest

baigm

Board Regular

Legacy 14611

Guest

baigm

Board Regular

Legacy 14611

Guest

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock