SUMPRODUCT using TABLE HEADERS

Question

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

Y EARMONTH	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

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?

Answer 1

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

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

Answer 2

Hi,

Without using structured references the formula I gave you is this

=SUMPRODUCT((B1:J1=A2:A4)*(B2:J4))

If we then convert that to a structured reference we get this

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

So the first bit in bold is the equivalent of B1:J1 and comprises of the table name (Table), the first row (Headers) and all the columns including and between Start and End. As I'm sure you know there's no need to create these names because Excel does this.

Likewise the underlined bit is the equivalent of A2:A4 where yearmonth is a name of a column and the last bold bit is the body of the table.

The short answer to your second question is no because there's text in the range and the bits that can be coerced into numbers aren't very useful when you do it. For example this

=VALUE(LEFT(Table[[#Headers],[2014-01]],4))*12

You can calculate anything you want from the table using structured references but still have to follow the same rules as when not using them.

returns the value of the header which is 24168 and as a date that equals 2 Mar 1966 but more importantly this

=VALUE(LEFT(Table[[#Headers],[START]],4))*12

tries to get the value of the header START and it has no VALUE, it's text and returns a #VALUE! error

Answer 3

Hi,

This one seems to work

=SUMPRODUCT(($C$1:$I$1=$A$2:$A$4)*($C$2:$I$4))

Here's a screenshot

Answer 4

Hello Mike,

It's actually almost 2am now so I will try out your suggestion tomorrow morning and give you your answer mark.

Can you please give me some insight as to what the syntax Table[[#Headers],[2014-01]:[2014-07]] suggests so that I can understand how to use it? I cannot understand what the ,[2014-01]:[2014-07] section means.

As far as the Start and End columns are concerned, I use them to bound the month columns since the user is allowed to add new month columns as required and I do not want to alter my formulas.

One more thing. In this notation, is it possible to use something like 

VALUE(LEFT(Table[[#Headers],[START]:[END]],4))*12+VALUE(RIGHT(Table[[#Headers],[START]:[END]],2))

• VALUE(LEFT(Table[YEARMONTH],4))*12+VALUE(RIGHT(Table[YEARMONTH],2))

in order to calculate the month offset for a different application I need?

Thank you in advance