How to sum specific cell ranges in Google Sheets?

Other Questions Google Sheets
1

I’m working on a Google Sheets project and I’m stuck. There’s a cell with the text “A1-A4, A8-A11”. I need to add up the values in the cells A1, A2, A3, A4, A8, A9, A10, and A11.

The result should be the total of these 8 cells. I know how to sum a continuous range like A1:A11, but I’m not sure how to handle the gap between A4 and A8.

Is there a way to parse this kind of range string and sum only the specified cells? Any help would be great!

Example:
If the values in those cells were:
A1 = 1, A2 = 2, A3 = 3, A4 = 4
A8 = 5, A9 = 6, A10 = 7, A11 = 8

The sum would be 36.

How can I make this work in Google Sheets? Thanks!

2

Here’s a solution that might work for you:

Use the SPLIT function to separate the ranges, then SUM and INDIRECT to calculate the total. Try this formula:

=SUM(INDIRECT(SPLIT(SUBSTITUTE(A1, " ", “”), “,”)))

Place this in a cell, assuming A1 contains your range string “A1-A4, A8-A11”. The SUBSTITUTE removes spaces, SPLIT separates the ranges, INDIRECT converts them to actual cell references, and SUM adds them up.

This method is flexible and will work with various range combinations. Just make sure your range string is formatted correctly. Let me know if you need any clarification on how this works!

3

yo, try this trick i found: =SUM(INDIRECT(REGEXEXTRACT(A1,“[A-Z][0-9]-[A-Z][0-9]”)))
it’ll grab those ranges and add em up for ya. just make sure ur cell format is like “A1-A4, A8-A11”. lemme know if it works!

4

I’ve dealt with similar issues before, and here’s a robust solution I’ve found:

=SUM(INDIRECT(TRANSPOSE(SPLIT(REGEXREPLACE(A1, “([A-Z][0-9]+)-([A-Z][0-9]+)”, “$1:$2”), “,”))))

This formula handles multiple ranges and is quite flexible. It uses REGEXREPLACE to convert the dash notation to the colon notation, which INDIRECT can understand. SPLIT separates different ranges, and TRANSPOSE ensures it works correctly.

One advantage of this method is that it can handle more complex range strings, like “A1-A4, B7-B9, D12-D15”. Just make sure your range string in A1 is formatted correctly.

If you’re dealing with large datasets, you might want to consider using array formulas or custom functions for better performance. But for most cases, this should work well.