How do I sum across a list of columns defined in an extra column or cell in excel

Question

Suppose columns A, B and C contain numbers. I want to sum everything in any of these columns if they are listed in an extra column or cell. The columns to be summed can be anywhere in the spreadsheet. And this must be a function rather than a VBA script.

For instance, column D could contain cells containing "A" and "C", or perhaps a single cell containing "A, C". My function (E2) would therefore output the sum of the values in columns A and C.

A   B   C   D   E
1   4   7   A   30
2   5   8   C
3   6   9

For bonus complication, I want the sum to be a SUMIF, in order to filter out the rows in columns A, B and C, based on a matching criteria. So, in the example below, I categorise the rows in columns A, B and C with a colour (F). E now displays two sums - the totals from columns A and C with category red (E2) and the sum with category green (E3). I fully expect the formulas in E2 and E3 to be different, in order to specify the different matching criteria.

A   B   C   D   E   F
1   4   7   A   18  red
2   5   8   C   12  red
3   6   9           green

Thanks.

Answer 1

If I've understood you correctly, I don't think SUMIF is the way to go.

I'd add a helper column in between your D & E, like this:

A   B   C   D   E   F
1   4   7   A   x   30
2   5   8   C   x
3   6   9       x

In column E, where I've added 'x' as a placeholder, enter this formula (in cell E2 and copy down):

=IFERROR(SUM(INDIRECT(D2&":"&D2)),0)

This simply sums the totals for each column you specify in column D as your criteria. Where there is no criteria for that row you have the fallback 0 called by IFERROR.

Finally in cell F2 you can sum all those subtotals:

=SUM(E:E)

Your final table will look like this:

A   B   C   D   E   F
1   4   7   A   6   30
2   5   8   C   24
3   6   9       0