I think I'm very close to what I want but I'm still getting an #N/A error -
I have some wage sheets that cross reference a labour table 'Table1' which stores the information of my employees (Pay code, Site, Contracted Hours etc). In Table1 I have columns titled 1-10 which values.
On the wage sheet I have a cell 'AM3' that will be a number between 1-10. Depending on that cell, the cell below should sum up all values in that column for all staff at that particular site.
For example - I have a wage sheet for site 'EXAMPLE SITE' which is stored in cell C2 and cell AM3 = 9.
I am trying to use the following formula to make this work:
=SUMIFS(INDEX(Table1,,MATCH(AM3,Table1[#Headers]),0),Table1[[Site]:[Site]],$C$2)
That is, I'm checking Table1, and finding the column headed with the value contained in cell AM3 (with an exact match). criteria_range1 is the Site column and criteria1 is 'EXAMPLE SITE' stored in C2.
I would expect this to sum every cell in column header 9, matching Site 'EXAMPLE SITE'. But I just get the #N/A error.
Table1:
Name - Site - 1 - 2 - 3 - 4 - 5 - 6 - 7 - 8 - 9 - 10
Tom - EXAMPLE SITE - 0 - 0 - 0 - 0 - 0 - 0 - 0 - 0 - 10 - 20
Geoff- EXAMPLE SITE - 0 - 0 - 0 - 0 - 0 - 0 - 0 - 0 - 9 - 18
Sarah- RANDOM SITE - 0 - 0 - 0 - 0 - 0 - 0 - 5 - 15 - 25 - 40
With that example I want the formula to return '19' as a numerical value. I feel like I'm just being dumb but no amount of googling is helping me.
MATCH seems to be having a hard time with matching table headers with numeric values. Try this:
=SUMIFS(INDEX(Table1,,MATCH(AM3,INDEX(Table1[#Headers]*1,),0)),Table1[[Site]:[Site]],$C$2)