A formula for subtotals against each category

[Vinay Vasu]

Hi,
I would like to have sub totals of Marks against each category and name (
there are diffrent names). please help me with a general formula.

Thanks in advance
Jeevan


Sr. No Qtn No. Name Test No Subject Correct Option Marked Option
Marks sub total Category
1 Apoorva 4 2 -0.25 Analytical
5 Apoorva 3 4 -0.25 Analytical
9 Apoorva 1 4 -0.25 Analytical
13 Apoorva 4 4 1.00 Analytical
17 Apoorva 4 4 1.00 Analytical
4 Apoorva 4 4 1.00 Argument
8 Apoorva 4 4 1.00 Argument
12 Apoorva 2 0.00 Argument
16 Apoorva 3 3 1.00 Argument
20 Apoorva 4 0 0.00 Argument
2 Apoorva 4 4 1.00 Conceptual
6 Apoorva 4 4 1.00 Conceptual
10 Apoorva 4 4 1.00 Conceptual
14 Apoorva 2 2 1.00 Conceptual
18 Apoorva 1 1 1.00 5.00 Conceptual
3 Apoorva 3 2 -0.25 Skill
7 Apoorva 2 2 1.00 Skill
11 Apoorva 4 4 1.00 Skill
15 Apoorva 4 4 1.00 Skill
19 Apoorva 3 3 1.00 3.75 Skill

 

[Per Jessen]

Hi

Use a Pivot Table.

Look at this site to get started:

http://contextures.com/CreatePivotTable.html

Regards,
Per

 

[JLatham]

Pivot Table is a good idea, but if space is at a premium on the sheet, you
can do it with a SUMPRODUCT() formula. Easiest way is to have 2 cells to
enter or choose the category and name to get the subtotal for in a 3rd cell.
For this example, assume:
Name selection in H1, category selection in H2, formula in H3. In your
table, names in column B, categories in column G, marks in column F:
=SUMPRODUCT(--(B1:B1000=H1),--(G1:G1000=H2),(F1:F1000))
adjust the row references as necessary to include all the data.