Referencing values of user defined value outside of a table

[tombomb]

Hi there,

I've tried to give an example of the problem in the attached image.

Capture.JPG

Intro
I have a table - the grey cells make up the header row.
It contains some data (columns 1-4)
To the right of this I'm showing Values depending on certain criteria (Columns S32, S33, S34)
The variable X is a user-defined within Name Manager - lets call the formula "Result"

The problem
In the totals regiion, I want to say:
=sumifs(Result,Table1[S32],(some criteria))

The value of Result is different in each cell.

I get a #Value error on "Result".
Essentially excel doesn't recognise I only want the sum of "Result" from each single column.

I've also tried SUMPRODUCT to the same effect.

The Question
How do I reference a user defined value within a specific range?
I want to be able to say something like:
(Table1[S32].Result)
But this doesn't work.

Any help much appreciated.

[Pauleyb]

I am unsure of how your spreadsheet is truly organized. If this goes much further, then please attach a sample worksheet which includes the result you desire (even if it is hand calculated).

My understanding is that you have a formula defined in the name manager called Result. I don't know if X is what you really have on your spreadsheet or if is a number. You call it a variable, so I'll assume it is a calculated number in each of the cells in the S-columns, since you can't sum an 'X'. And, I'll assume that X is generally a different value in each of those columns.

When you use SUMIFS, the first value needs to be a range. It does not sound like Result is a range, but is a value. I don't see why this would not work for you:
=sumifs(Table1[S32],Table1[S32],criteria...)

[tombomb]

Hi Pauley,

You're absolutely correct with your assumptions and your understanding of my sheet.
The mistake was mine in not fully defining the problem.

As you rightly say, X isdifferent in each cell and is the result of the "Result" formula.

The problem is that X is not the only thing present in the S32-34 columns. The contents of those cells are actually a concatenation of a number of results to different formulae. So in my original definition i should have said that the cells in columns S32-43 actually read: "=result&" / "&anotherformula1&" / "&anotherformula2.

Therefore I can't simply sum the contents of those columns...

Any thoughs?

[MrShorty]

This is why I don't like to concatenate "result" with other text strings in a single cell. This would probably be easy if you had kept "result" separate from the other parts of the final text string.

Assuming the final text strings in S32:S34 are essential to the spreadsheet, I would put "result" in a helper column. This helper column is then the =result alone, and your formulas that need to "sum" results can simply refer to this helper column. Columns S32 can be =helper column & another formula & another formula. This strategy keeps the information in result by itself where it is more easily accesible to other functions.

If you are desperately opposed to using a helper column, then you will need to use Excel's text functions (LEFT(), MID(), etc) to "undo" the concatenation in S32:S34 inside of your "sum" function. https://support.office.com/en-us/art...__toc309306717 IMO, it is much easier to use a helper column.

[Pauleyb]

I concur with Mr. Shorty. There is no shame in having a hidden helper column, especially if it makes other tasks simpler. MAYBE we could come up with some nifty formula that extracts the values you want from the cells and then sums them, but nothing easy comes to mind and I start thinking of a macro.

You may be able to use some custom formatting and creative cell formatting to get something that looks similar to what you are presenting. For the purpose of this 'guide' lets assume you currently have this concatenated text and formulas in one of your columns.
X/Y/Z

Instead of one column, use three columns. The first column will be your calculated X and then right justified in the cell. The middle column will be Y and centered in the cell and using this custom number format "/"#"/". The last column would be your Z and left justified in the cell. I know it will not look exactly the same, but you can then sum up those numbers to your heart's content.