Solved

# Add up Totals from a list

Posted on 2012-08-11
Medium Priority
330 Views
Hello,

I have a spreadsheet as attached.

On Sheet 1 I have names listed from B13 downwards - these names change day to day - there may be 5 or 40 however each name on this list always remains unique.

On Sheet 2 I have the names (the same names as listed on Sheet 1 but on many rows) in column F. On the same row as each name in column J  I have a number.

I would like to, via VBA, go down the list of names as shown on sheet1 from B13 down (until there are no more names) and against each name in C13, 14 etc.. put a total.

The total would be a sum of column J (from sheet2) where that name appears in column (from sheet2) and shown in a total on Sheet 1 in B13,14 etc for each name.

Sorry If this is not clear, please let me know if I need to help explain it any further ;)

Thanks,

GISVPN
Example-1.xlsx
0
Question by:gisvpn

LVL 43

Expert Comment

ID: 38283815
Try

``````Sub subtotals()
Range("C13", Range("B" & Rows.Count).End(xlUp).Offset(, 1)).FormulaR1C1 = "=sumif(sheet2!c6,rc[-1],sheet2!c10)"
Range("C13", Range("B" & Rows.Count).End(xlUp).Offset(, 1)).Value = Range("C13", Range("B" & Rows.Count).End(xlUp).Offset(, 1)).Value
End Sub
``````
0

Author Comment

ID: 38284133
That does not seem to work.

I get a prompt to save the workbook each time I run the macro.

Can I specify in the code this is to look only at C13 downwards for any names in B13 downwards only on the Summary tab of the workbook.
I like the idea of a formula being used; )
0

LVL 43

Expert Comment

ID: 38284166
You ought to be running the code from the summary sheet and also make sure that the name of the data sheet is sheet2. If the data sheet has some other name then change all instances of sheet2 in the formula to whatever name you have.

Have you tried the macro on the file you uploaded?
0

LVL 24

Expert Comment

ID: 38284284
Attached is a file which puts the sumif formula in the cell C13 onward.
this is after creating list with hyperlinks to sheets.

See attached:
``````Sub DataNamesMake()
Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook
Set ws = wb.Sheets("Summary")
Set ws2 = wb.Sheets("data")
x = ws.Cells(Rows.Count, 2).End(xlUp).Row + 1
y = 1

Do While Len(ws2.Cells(y, 6)) <> 0
If WorksheetFunction.CountIf(ws.Range("B:B"), ws2.Cells(y, 6).Value) = 0 Then
ws.Cells(x, 3).FormulaR1C1 = "=SUMIF(Data!C[3],RC[-1],Data!C[7])"
x = x + 1
End If
y = y + 1
Loop
End Sub
``````
Example-1.xlsm
0

LVL 24

Accepted Solution

Steve earned 2000 total points
ID: 38284292
Following code clears rows 13-999 before re-entering the names and adds a static sum.

``````Sub DataNamesMake()
Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook
Set ws = wb.Sheets("Summary")
Set ws2 = wb.Sheets("data")

ws.Rows("13:999").Delete
x = 13
y = 1

Do While Len(ws2.Cells(y, 6)) <> 0
If WorksheetFunction.CountIf(ws.Range("B:B"), ws2.Cells(y, 6).Value) = 0 Then
ws.Cells(x, 3) = WorksheetFunction.SumIf(ws2.Range("F:F"), ws.Cells(x, 2), ws2.Range("J:J"))
x = x + 1
End If
y = y + 1
Loop
End Sub
``````
All depends on how you want the file to work.
Example-1.xlsm
0

LVL 34

Expert Comment

ID: 38287070
Or you could set up a pivot table.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst youâ€™ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to dâ€¦
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
###### Suggested Courses
Course of the Month15 days, 7 hours left to enroll