I am trying to build a formula using multiple references to other cells. The easiest way to do this seems to be to build the formula as a string and then evaluate the string. However Excel no longer has an EVAL function.

More specifically, I have a table like so:

Type	Jan	Feb	Mar	Apr
Apple	10	20	15	30
Orange	8	11	17	21
Apple	4	6	9	15
Orange	5	10	15	20

It's more complex, but this is the gist. I want to be able to sum multiple rows, from a given month through the end of the year for all of a type. I can't use a pivot table, as I'm using this in another table with other calculations. And I'd like to be able to change the month without breaking the formula.

I'm currently using sumproduct: SUMPRODUCT( Tbl1[[Jan]:[Apr]] * (Tbl1[Type]="Apple") )

This works fine for January. But I'd like to be able to use the same formula for Feb, Mar, etc. I'd like to use another cell as a reference for the starting month. So in the above formula, change Jan to a reference to another cell. Unfortunately, Excel doesn't seem to recognize using a cell reference there.

To make it slightly more complicated, the table is in another tab.

I could build it as a string easily and evaluate it, but that ability seems to be gone.

Alternatively, is there another way to structure this?

Thanks!

CodePudding user response：

I would flat the structure (through unpivot in powerquery), add numbers to months so it can be sorted/sliced easily and used the sumproduct with start month & end month

CodePudding user response：

You don't necessarily have to change the range, you just need to add another condition to select the months.

A simple solution you could do is to create a separate table for the month options, and pass the options into the SUMPRODUCT formula you already have.

Here are a couple examples.

• My table's name is DATA and the option table for the month names is named OPTIONS_MONTHS.

---

Example 1: Simple Options

• Option table for the months:

• With Apr added:

• Formula:

I just added an extra criteria to your formula:

       | =SUMPRODUCT( 
       |    DATA[[Jan]:[Apr]] * (DATA[Type]="Apple")    
Here > |    * (IFERROR(DATA[[#Headers],[Jan]:[Apr]]=TRANSPOSE(OPTIONS_MONTHS), FALSE)) 
       | )

---

Example 2: Include/Exclude Options

You can even spice it up a notch and include all of the months in the options table and use booleans to include/exclude the values so you don't have to re-type the month's name, and to prevent typos and some other errors.

• Include/Exclude field:

Jul is already set to True but the formula only selects the rows with "Apple".

• Changed Sep to True:

2 was added from the Sep column.

• Formula:

The formula is adjusted slightly here for the additional fields.

       | =SUMPRODUCT( 
       |    DATA[[Jan]:[Dec]] * (DATA[Type]="Apple")
Here > |    * (DATA[[#Headers],[Jan]:[Dec]]=
Here > |       TRANSPOSE(
Here > |         IF(OPTIONS_MONTHS[Include], OPTIONS_MONTHS[Months], FALSE))
Here > |       )
       | )

---

I would even recommend doing this with the Type field too and add some data validations.