[Solved] Excel Formula Question

[Michelle11]

I have the below spreadsheet for 25 times for a small league that I am running. I need to take where it says number and put it to the total spots for 25 times like this. How would I do that? Do you have any ideas? Also it goes down 30 lines so there is multiple pages of this as well. There is on the first page 4 like this and then 3 on each additional page like this but each has to have the running total.

Words	Words	number	Words	Words	number	Total	Words	Words	number	Total

 

[Seemanto Saha]

Hello Michelle11,

Thanks for sharing your problem with us. I understand that you want to take values from number columns and put them into the Total columns for each table in every worksheet.

As you require 25 tables with 4 tables on the first page and 3 tables on the remaining page, we will require 8 worksheets. I prepared the following tables:​

We will use a simple VBA code to calculate the total value for each number we find and place it in the subsequent Total column.

First, go to the Developer tab and click the Visual Basic option. If you don’t have the Developer tab, then use the keyboard shortcut Alt + F11 as an alternative.​

Then click the Insert tab and select the Module option.

Enter the following VBA code in the module. Save and Run this code.

​

Sub SumNumbersInTotalSpot()
    
    Dim last_row As Integer
    Dim last_col As Integer
    Dim total_val As Integer
    Dim i As Integer
    Dim j As Integer
    
    For Each wrksht In ThisWorkbook.Sheets
        last_row = wrksht.Cells(Rows.Count, 1).End(xlUp).Row
        last_col = wrksht.Cells(1, Columns.Count).End(xlToLeft).Column
        For i = 1 To last_row
            total_val = 0
            For j = 1 To last_col
                If IsNumeric(wrksht.Cells(i, j).Value) Then
                    total_val = total_val + wrksht.Cells(i, j).Value
                End If
                If wrksht.Cells(1, j).Value = "Total" And total_val <> 0 Then
                    wrksht.Cells(i, j).Value = total_val
                End If
            Next j
        Next i
    Next wrksht
End Sub

After executing this code, all numbers will be summed and placed in the subsequent cells of the Total column.​

I hope I was able to provide a sufficient answer to your problem. If you have any other queries for this problem, please share a sample dataset with manual calculations of your problem.​

Let us know your feedback. The workbook used for this problem is attached below.

Regards,
Seemanto Saha
ExcelDemy