# How to use formula values present in another cell in a formula

I am using the formula from this question

``````=ArrayFormula(sum(sumif(A:A,{"Bus";"Train"},B:B)))
``````

I am planning to use it for multiple categories. For example the above can a sum of my `travel` category expenses. I want to sum my entertainment expenses. It will have a formula like

``````=ArrayFormula(sum(sumif(A:A,{"Movie";"Games"},B:B)))
``````

The only difference between these will be the thing in the brackets. It would be great if I could place the array value in a cell like below

``````   .       C               D
1  | category     | category names    |
2  | travel       | {"Bus";"Train"}   |
3  |entertainment | {"Movie";"Games"} |
``````

And then use my formula like this

``````=ArrayFormula(sum(sumif(A:A, D2, B:B)))
``````

Same for `A:A`. I want to do sum every month so the only thing that is going to change will be `A1:A100` for first month, `A101:A:200` for second month and so on.

So how do I use values from other cells in my formulas?

## Alternative 1

Instead of `{"First Value;"Second Value"}` write `={"First Value,"Second Value"}` in cells `D2` and `D3` in the example provided in the question.

Then, instead of using a category names reference of the form `D2` use the `D2:D3` reference form.

## Alternative 2

Instead of `{"First Value;"Second Value"}` write `First Value;Second Value` in cells `D2` and `D3` in the example provided in the question.

Then, instead of D2 as the reference in the formula use `=SPLIT(D2,";")`

# Explanation

1. When an array in wrote in a cell without a leading formula operator (`=` or `+`) it is interpreted by Google Sheets as a string instead as an array.

## Alternative 1

1. As the OP want to put one array in each row, instead of using a row separator, use a column separator.

2. As the array values are in a 1x2 range the reference to use in the formula should be of the form `D2:E2`. The formula will be like the following

`=ArrayFormula(sum(sumif(A:A, D2:E2, B:B)))`

### Example ## Alternative 2

In order to make easier to convert a string to an array it's suggested to remove the enclosing braces and the quotes, otherwise more functions will be required to remove them and will make the spreadsheet more complex and slow.

The formula will be like the following:

`=ArrayFormula(sum(sumif(A:A, SPLIT(D2,";"), B:B)))`

### Example Here is one option for you

``````=sum(filter(A2:A, match(B2:B, split(D2,";"), 0)))
``````

where

• D2 is a ; delimited array, i.e. "movie;video games"
• `split(D2,";")` convert D2 to an array: {"movie","video games"}
• test condition `match(B2:B, split(...), 0)` checks for B values that is "movie" or "video games"
• `filter(A2:A, match(...))` gives you the corresponding A values based the the test condition
• `sum( filter(...) )`, well, you know about it

Note you can't have `split()` in `arrayformula`. This appears to be a bug.

Here's an illustration: 