Mix between vlookup and sum command?

Copper Contributor

Hello, I am making an excel spreadsheet for my fiancé who is a first year teacher. She needs to report grades every 10 weeks. There are kids who are regular students (Denoated as ‘REG’). There are also other categories for students with learning disabilities and whatnot which have a different 3 letter phrase. Her school wants to know how many regular students are between a 0-54 average, how many are between 55-65. And how many are 65+. They also want to know how many students are in these ranges for the other disabilities separately. So I created a sheet where you input “REG” or “ELL”, etc. in one column next to said students name. This indicates if they have a disability or not. The students grade average is then in the cell to the left of this. I now want a command to lookup how many students that are ‘REG’ and have an average between 0-54 and spit out the sum of the number of students that fit this criteria. I would report this command for it to spit out the sum of the number of students that are regular that have the other range of grades in different boxes. I just can’t come up with a command. Any help is appreciated.

2 Replies
It is better go with Sumifs with Index match function. Incase only two columns are there, you can for sumif with index match function. More than two columns better go for sumifs with index match function.
sumif with index match
=SUMIF(Range,Criteria, INDEX(Range,0,MATCH(lookup,Range,0)))
More than 2 columns to match
=SUMIFS(INDEX(Range,0,MATCH(Lookup value, search range,0)),Criteria range1, Criteria1, Criteria range2, Criteria 2)
Based on data, criteria 1 and 2 can be decided.


@DanTheeMan11 It sounds like you're looking for a count of students by category and by grade range. I would recommend the COUNTIFS function for this. Please see the attached sample workbook to help get you started...