Calculating Totals for Specific Columns in Excel

Question

A spreadsheet contains details from work done for the week, where each job occupies 10 columns and there are 30 jobs per day, resulting in a total of 210 jobs. The columns extend to 2120. At the end of the columns, totals are needed for specific items like Our KMs and Extra Hours. Each week, a new row is created for the next week's work.

Currently, Named Ranges have been used for columns, such as Column 27 (from Row 4 to 104), and the formula +SUM(Name_1+Name_2+…) has been applied in the corresponding row for the work. However, this results in the total of all the columns, while the goal is to total only the columns in that row that contain Our KMs (i.e., columns that end with a 7).

The layout of the spreadsheet looks like this, with column numbers indicated:

21	22	23	24	25	26	27	28	29	30
Date	Time	Reg	Driver	Tour	Client KMs	Our KMs	Details	Extra Hours	Type Shift

31	32	33	34	35	36	37	38	39	40
Date	Time	Reg	Driver	Tour	Client KMs	Our KMs	Details	Extra Hours	Type Shift

Answer 1

Hi Bob Spence,

Thank you for contacting us regarding your Excel spreadsheet. As per your description, you would like to calculate the total of "Our KMs" values in each row, where each job spans 10 columns, and "Our KMs" always falls in columns with numbers ending in 7 (e.g., columns 27, 37, 47, etc.). 

To resolve this, please follow the steps below: 

1. Go to the row you want to calculate the total for (for example, row 4). 
2. In the cell where you want to display the total (e.g., the last column of that row), paste the following formula: 

=SUM(FILTER(A4:ZZ4, MOD(COLUMN(A4:ZZ4),10)=7)) 

 

This formula works with Excel 365 or Excel 2021. 

 It sums only the values in columns where the column number ends in 7 — which correspond to the "Our KMs" values.   

Replace A4:ZZ4 with the actual range for your data if needed (e.g., A5:ZZ5 for row 5). 

1. Copy the formula down to other rows if you need totals for multiple weeks. 

 

• COLUMN(A4:ZZ4) returns the column numbers. 
• MOD(...,10)=7 filters the columns ending in 7. 
• FILTER(...) returns only the relevant cells from the row. 
• SUM(...) calculates the total. 

 

If you're using an older version of Excel that doesn’t support the FILTER function, feel free to reply and we’ll help you with an alternative using helper columns or VBA. 

If the information provided doesn’t fully address your situation, please feel free to share more details so I can offer the most appropriate solution for your specific needs. 

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.