0

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). enter image description here

Improve this question
2
  • 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))
    – Tim
    Jul 21, 2016 at 17:00
  • @Tim - that's how it works now, I'm trying to change it in some way to make it so the individual cells never have to be specified in the formula, but rather it just somehow identifies every cell it needs to add by looking at the label at the top (I, II, III) and sides (Open, Closed, N/A) for every chart that may be present. Pretty sure it could be managed in vba but I'd rather do it as a formula if possible.
    – Kefka
    Jul 21, 2016 at 17:03

2 Answers 2

Reset to default
0

In short, there is no way to do this without modifying your chart layouts that isn't better than specifying far more cells than you will ever use. In my example above, I end at cell J500003. Suppose your charts end at row 470. This is not a problem. Empty cells count as 0 and do not affect the sum. If you make the formula arbitrarily high to start with, you never have to modify the formula again.

If you are open to a minor addition to the layout of the charts, the formula can be shorter, but will still end up specifying a large number of unused cells. If you add "Header" (or "Status" or anything) in the cell above Open, then you can treat those columns as a database and use DSUM

For example, here's the new layout:

    A       B       C   D   E   F
12 Header           I   II  II  Total
13 Open             1   75  21  97
14 Closed           21  312 39  372
15 N/A              2   47  7   56
16 Unreviewed                   
17 Total possible                   
18 Total Reviewed   24  434 67  525
19 Percent Complete                 
20
21                  
22                  I   II  II  Total
23 Open             1   75  21  97
24 Closed           21  312 39  372
25 N/A              2   47  7   56
26 Unreviewed                   
27 Total possible                   
28 Total Reviewed   24  434 67  525
29 Percent Complete

(You really only need to add your string of text in cells A12 and H2. I've used "Header" in my example layout.)

Now in A1 specify the column you want and in A2 specify the value to match in that column. For example, I want to add all the "I"'s that are "Open", so A1 is "Header" (because that's the name I gave the column of labels) and A2 will be "Open" and the formula will then be =DSUM(A12:F140000,"I",A1:A2)+DSUM(H2:M140000,"I",A1:A2) It makes for a much shorter formula, but it isn't any7 more or less efficient and still references potentially unused cells.

If you want to avoid touching unused cells entirely and don't want to add text to two cells, then you'll have to resort to looping with VBA.

Improve this answer
0

The following formula will work, but only if you change some of your formatting.

Formatting changes needed:

  1. The summations cannot happen within the range of cells you want to calculate. So you can switch your formatting to something like this

Image 1

Once you have your summations outside the main body, the following formula will work just fine. It would be in C4 and then copy-pasted into the other cells. (The formula is an array formula, and should be entered with CTRL-SHIFT-ENTER)

{=SUM(IF(COLUMN($A$12:$M$25)=(IF($A$12:$M$12=C$3,COLUMN($A$12:$M$12))),
IF(ROW($A$12:$M$25)=IF($A$12:$A$25=$A4,ROW($A$12:$A$25)),$A$12:$M$25)))}

Expand $A$12:$M$25 to the larger array you want to cover, and make sure $A$12:$M$12 is the entire first row of the array and $A$12:$A$25 is the entire length of the array.

This formula will only work as long as you maintain the grid pattern, because it only looks at the I, II, III on the first row and the "open" "closed" and "N/A" on the first column, then calculates the intersections.

To explain the formula a little more:

  1. The embedded if statements: the IF($A$12:$M$12=C$3 finds the appropriate columns and the IF($A$12:$A$25=$A4 statement finds the appropriate row.

  2. The outer if statements: IF(COLUMN($A$12:$M$25) and IF(ROW($A$12:$M$25) return which cells meet both criteria

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .