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

#### JAzzyD

##### New Member
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

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### Marcelo Branco

##### MrExcel MVP
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

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

Thank You
M

#### Marcelo Branco

##### MrExcel MVP
You are welcome. Glad to help

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

M.

Replies
3
Views
1K
Replies
11
Views
2K
Replies
1
Views
733
Replies
1
Views
443
Replies
5
Views
250

1,195,593
Messages
6,010,624
Members
441,558
Latest member
lambierules

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back