Marks Marks

4 4
3 3
6 6
4 4

SUM 17 17
AVERAGE 4.25 4.25
MAX 6 6
MIN 3 3
 Marks SUMIF
Nikhil 2 Nikhil 6
Gaurav 5 Gaurav 10
Praveen 7 Ankur 3
Gaurav 5 Praveen 14
Nikhil 1
Ankur 3 =SUMIF(A2:A11,E2,B2:B11)
Praveen 5 =SUMIF(A3:A12,E3,B3:B12)
Nikhil 3 =SUMIF(A4:A13,E4,B4:B13)
Praveen 5 =SUMIF(A5:A14,E5,B5:B14)
Praveen 4 #N/A

SUM 40
Student Name Subject Marks
Nikhil English 6 Nikhil
Gaurav Maths 4 Gaurav
Ankur English 3 Ankur
Gaurav Science 9 Praveen
Nikhil Maths 7
Ankur English 2
Praveen Maths 1
Nikhil Science 5
Praveen English 7
Praveen Science 4
Nikhil English 6
Gaurav Maths 4
Ankur English 3
Gaurav Science 9
Nikhil Maths 7
Ankur English 2
Praveen Maths 1
Nikhil Science 5
Praveen English 7
Praveen Science 4
English Maths
12 14
0 8
10 0
14 2

=SUMIFS($C$2:C21,$A$2:A21,F2,$B$2:B21,$G$1) =SUMIFS($C$2:C21,$A$2:A21,F2,$B$2:B21,$H$1)
=SUMIFS($C$2:C22,$A$2:A22,F3,$B$2:B22,$G$1) =SUMIFS($C$2:C22,$A$2:A22,F3,$B$2:B22,$H$1)
=SUMIFS($C$2:C23,$A$2:A23,F4,$B$2:B23,$G$1) =SUMIFS($C$2:C23,$A$2:A23,F4,$B$2:B23,$H$1)
=SUMIFS($C$2:C24,$A$2:A24,F5,$B$2:B24,$G$1) =SUMIFS($C$2:C24,$A$2:A24,F5,$B$2:B24,$H$1)
Science
10
18
0
8

=SUMIFS($C$2:$C$21,$A$2:$A$21,F2,$B$2:$B$21,$I$1)
=SUMIFS($C$2:$C$21,$A$2:$A$21,F3,$B$2:$B$21,$I$1)
=SUMIFS($C$2:$C$21,$A$2:$A$21,F4,$B$2:$B$21,$I$1)
=SUMIFS($C$2:$C$21,$A$2:$A$21,F5,$B$2:$B$21,$I$1)

function +f4 freezes the row/column

Nikhil
Ankur
Praveen 1
Nikhil
Praveen
Praveen
Nikhil COUNTIF
Gaurav Nikhil 3
Ankur Gaurav 2
Gaurav Ankur 2
Nikhil Praveen 3
Ankur
Praveen
Nikhil
Praveen
Praveen
Year Product Cost Max for Oranges
2017 Oranges 12.25
2018 Oranges 15.99 Max for Oranges since 2019
2018 Bananas 10.5
2019 Apples 12.5 Max for 2020
2019 Bananas 8.35
2019 Oranges 13.45 Max for Products starting with A
2020 Apples 18
2020 Apricots 6.5
2020 Oranges 4.95
Year Product Cost Min for Apples
2017 Oranges 12.25
2018 Oranges 15.99 Min for Bananas since 2018
2018 Bananas 10.5
2019 Apples 12.5 Min for 2018
2019 Bananas 8.35
2019 Oranges 13.45 Min for Products starting with A
2020 Apples 7.95
2020 Apricots 6.5
2020 Oranges 4.95
 Marks AVERAGEIF
Nikhil 2 Nikhil 2
Gaurav 5 Gaurav 5
Praveen 7 Ankur 3
Gaurav 5 Praveen 5.25
Nikhil 1
Ankur 3
Praveen 5
Nikhil 3
Praveen 5
Praveen 4

AVERAGE
Student Name Subject Marks
Nikhil English 6 Nikhil
Gaurav Maths 4 Gaurav
Ankur English 3 Ankur
Gaurav Science 9 Praveen
Nikhil Maths 7
Ankur English 2
Praveen Maths 1
Gaurav English 3
Nikhil Science 5
Praveen English 7
Praveen Science 4
Nikhil English 6
Gaurav Maths 4
Ankur English 3
Gaurav Science 9
Nikhil Maths 7
Ankur English 2
Praveen Maths 1
Nikhil Science 5
Praveen English 7
Ankur Science 6
Ankur Maths 2
Praveen Science 4
English Maths
6 7
3 4
2.5 2
7 1
Science
5
9
6
4