SUMPRODUCT using TABLE HEADERS I am trying to use SUMPRODUCT with a condition th

Question

SUMPRODUCT using TABLE HEADERS

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 ressult of 200+240+300, by comparing the ffield 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 Colummn A

What am I doing wwrong???

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

Hi,

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]]))

Hi,

It works for me like thiis

=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[[#Heeaders],[2014-01]:[2014-07]]=Table[YEARMONTH])*(Table[[2014-01]:[2014-07]]))

Hi,

It works for me like thiss

=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]]))

EDIT.....Forgot to mention I use a , delimiter so you'll have to change to your ;

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

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