Adding Two Ranges for a Single Bar in a Chart

[nkapran]

I have two ranges that I would like to add together for displaying in a bar graph. The data is laid out such that the X axis labels will be 7 sections for fiscal quarters (i.e., '22 Q4, '23 Q1, '23 Q2, etc.) and then subdivided into teams (so 6 bars per quarter, with each quarter being a set of bars on the X-axis). The data feeding each bar will be range D120:AS120 and D121:AS121, such that the first bar in the first section is D120+D121, the second will be E120+E121, etc. Normally, I would sum these values using an array formula {=D120:AS120 + D121:AS121}, but I cannot get the chart Data Source > Series Values field to accept an array formula.

I could simply sum these values into another row, then point the chart to that row for the data, but I want to save on space.

How can I do this?

[#2]

@nkapran Why save on space? You have over a million rows at your disposal. And if it's really important you can hide the rows you don't want to show.

[#3]

@Riny_van_EekelenSimply put, because I want to make things as programmatic as possible. Part of the problem I'm running into with the systems I have inherited is that EVERYTHING is hardcoded, especially in Excel. No named ranges, no dynamic formulas, etc. Part of why I want this to be formulaic is because A) we can't assume that the amount of data that we want in the chart right now is going to be constant, and B) I want to convert all of the data sources into named ranges, so the more I can have the calculations happening in formula, the fewer ranges I need to make and the fewer names I need to juggle. Why make a new named range for one specific calculation which might not even be the one I need in the future?

So, thanks for the suggestion, but I still need an answer to my specific question.

[#4]

I'm still looking for a solution to this problem; does anyone have any ideas?

[#5]

@nkapran Sorry for having left the issue hanging a bit. I simply couldn't visualise what you were dealing with. Re-read you postings and had an idea that might help you.

Try creating a Stacked bar chart and match the colour of both series. Then you can create something like this:

It looks like a regular bar chart based on one series, but actually it took two without the need to sum them. File attached.

[#6 · Accepted solution]

@nkapran 

Whilst a Series definition might not accept an array formula, it will accept a defined name (sheet local names are useful because the name must be fully qualified by its parent sheet or book, and users tend to create shorter names on sheet tabs than for file names).  The Name then contains the formula.

Note1: The concept of a named range is only a special case of the named formula in which the name happens to resolve to a sheet reference.  

Note2:  In extreme cases, complex charts can be created with no numbers coming from worksheets.

[#7]

Thank you to both of you, both of these solutions would work for me. I'm surprised I didn't think of something as simple (but effective) as "just color the bars the same" XD. But, I also didn't think about creating named ranges that exist to be formula holders, either, and that's very useful. Either or both of these would work.