A formula for subtotals against each category
 

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

A formula for subtotals against each category
 

Hi

Use a Pivot Table.

Look at this site to get started:

http://contextures.com/CreatePivotTable.html

Regards,
Per



On 18 Apr., 06:58, "Vinay Vasu" wrote:
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

A formula for subtotals against each category
 

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.

"Vinay Vasu" wrote:

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


.