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.