Prev: Worksheet toolbar
Next: "Move to bottom of range"
From: Vinay Vasu on 18 Apr 2010 00:58
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


From: Per Jessen on 18 Apr 2010 01:24
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" <vinayv...(a)hotmail.com> 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

From: JLatham on 18 Apr 2010 09:36
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
>
>
> .
>
 | 
Pages: 1
Prev: Worksheet toolbar
Next: "Move to bottom of range"