So I have a spreadsheet with a bunch of little mini-charts as seen below. The numbers in blue are user entered, and they are all added together in the chart with the gray cells on the top left.
Right now, I do this by having a basic sum formula. The real spreadsheet is much much larger, but in the example C3 would be =sum(J3+C13+J13), D3 would have =sum(K3+D13+K13), and so forth. As you can imagine, it is a PITA to add new charts or remove existing ones.
What I would like is a way to make this happen automatically without having to add up individual cells, so I could add or remove as many charts as I want while still having the numbers added up.
So it would be like, cell E5 would count all cells in a sheet where a number is three cells below III and three cells to the right of N/A. Or something that accomplishes the same thing.
Is there a way to accomplish this without changing the layout of my spreadsheet?
(ignore the N/As, I just copied and pasted to make a simpler example picture without modifying formulas that broke when I did that).
Sum
will add noncontiguous ranges. Presuming your tables are always laid out exactly the same and there will never be any extra data around your tables, you can easily make your formula=Sum(J3, C13, J13, C23, J23, ..... ,C500003, J500003, etc)
(As a side note, your example,=sum(J3+C13+J13)
, is adding the 3 cell values together then summing that single number because you've used plusses inside the sum function. sum is redundant in your example, and it could just as easily be written as=J3+C13+J13
. To rewrite to use sum instead, use commas like this:=sum(J3,C13,J13)
)