Excel Sum dynamic range

0
votes

I would like to calculate the sum of a column total in a table, base on the name of the column header.

For example, below is a table of monthly sales by each person (from cell A4 to M7).

I would like to calculate the total monthly sales in Cell D13 with the month as an input variable (as in Cell B13).

enter image description here

My thought is =SUM(INDEX(B5:M7,MATCH(B13,B4:M4,0))) where I tried to use index and match function to find the range, then use sum function. It returns with an error and I am currently stuck here.

Your help is appreciated.

enter image description here

1
excelexcel-formula

1 Answers

0
votes

Using SUMPRODUCT function

In D13 enter formula :

=SUMPRODUCT((B4:M4=B13)*B5:M7)