Using SUMIFS with MATCH AND INDEX - how to use - help please

[JAzzyD]

I have an excel spreadsheet with columns of data with the first row containing column headers - age, gender, scores, absences FSM Jan etc.

I have managed to use countif to count number of pupils that are female on FSM using the following:

=COUNTIFS(INDEX('Table of Pupil Data'!$A$1:$BU$7297,,MATCH("Gender",'Table of Pupil Data'!$A$1:$BU$1,0)),"Female",INDEX('Table of Pupil Data'!$A$1:$BU$7297,,MATCH("FSM Jan",'Table of Pupil Data'!$A$1:$BU$1,0)),"FSM")

Now I would like to SUM the scores (in column with header SCORE) for all pupils that are female (column header Gender) and on FSM (column header FSM Jan). All data is sheet called Table of Pupil Data.

I am struggling to define the sum range by referencing column header. I want to use column headers to define columns for summing and criteria.
Any help greatly appreciated.

Thanks
JazzyD

 

[Marcelo Branco]

Maybe...

=SUMIFS(INDEX('Table of Pupil Data'!$A$1:$BU$7297,,MATCH("SCORE",'Table of Pupil Data'!$A$1:$BU$1,0)),INDEX('Table of Pupil Data'!$A$1:$BU$7297,,MATCH("Gender",'Table of Pupil Data'!$A$1:$BU$1,0)),"Female",INDEX('Table of Pupil Data'!$A$1:$BU$7297,,MATCH("FSM Jan",'Table of Pupil Data'!$A$1:$BU$1,0)),"FSM")

Hope this helps

M.

 

[JAzzyD]

Perfect thank you.
Is there a better way to do this in excel? Another function?

Thank You
M

 

[Marcelo Branco]

You are welcome. Glad to help

AFAIK this is the proper way to achieve what you need.

M.