Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A SUB CATEGORY USING DATA VALIDATION? ALRDY USNG DV FOR CATEGORY | Excel Discussion (Misc queries) | |||
Formula for category ratio & numbers | Excel Discussion (Misc queries) | |||
-PLS CONSIDER THIS AS URGENT! HOW TO PUT SUM FORMULA IN VB FOR A CHANGING MULTI CATEGORY OR GROUP | Excel Programming | |||
How do I create a list of costs and get subtotals of each category | Excel Discussion (Misc queries) | |||
Add a category to the Insert-Function category list | Excel Programming |