I am trying to use SUMPRODUCT with a condition that references the TABLE Header

Question

I am trying to use SUMPRODUCT with a condition that references the TABLE Header of the values summed.

YEARMONTH

START

2014-01

2014-02

2014-03

2014-04

2014-05

2014-06

2014-07

END

I am trying this to get the result of 200+240+300, by comparing the field YEARMONTH of every data row with the corresponding column name of the values to be summed, but I only get a value of 0 (zero)

{=SUMPRODUCT(IF(INDEX(TABLE[#HEADERS];0;COLUMN(TABLE[START]:TABLE[END]))=TABLE[YEARMONTH];1;0)*(TABLE[START]:TABLE[END]))}

My TABLE starts at Column A

What am I doing wrong?

Any suggestion??

YEARMONTH

START

2014-01

2014-02

2014-03

2014-04

2014-05

2014-06

2014-07

END

2014-04 100 100 150 200 250 2014-05 180 220 240 260 2014-06 120 150 250 300 400

Explanation / Answer

It works for me like this

=SUMPRODUCT((Table[[#Headers],[START]:[END]]=Table[YEARMONTH])*(Table[[START]:[END]]))

You don't actually need the Start and End columns and if you delete them the formula becomes this

=SUMPRODUCT((Table[[#Headers],[2014-01]:[2014-07]]=Table[YEARMONTH])*(Table[[2014-01]:[2014-07]]))

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.