| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count and Sum functions with 2 criterias
Question:
Category Processed (Yes) Not Processed(No) Count Sum(Amt) Count Sum(Amt) 1 - 25 26 - 50 51 - 100 101 - 250 Total Data has two columns which are Amount and Status(Yes/No). Can someone help me with the formulae to count the number of coumns which fall in each Category and also sum of the amounts in those categories? -- Sue |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count and Sum functions with 2 criterias
Your question is not clear, at least to me.
I am not seeing where the variable data is and hat is repeated, so what needs to be counted/summed? -- __________________________________ HTH Bob "Sue" wrote in message ... Question: Category Processed (Yes) Not Processed(No) Count Sum(Amt) Count Sum(Amt) 1 - 25 26 - 50 51 - 100 101 - 250 Total Data has two columns which are Amount and Status(Yes/No). Can someone help me with the formulae to count the number of coumns which fall in each Category and also sum of the amounts in those categories? -- Sue |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count and Sum functions with 2 criterias
Let me give an example, Say the Data looks like this:
Category Processed 2 Yes 50 No 40 Yes 25 Yes 90 No 7102 Yes 198 Yes 648 No The Format that I would like is as follows: Category Processed (Yes) Not Processed(No) Count Sum(Amt) Count Sum(Amt) 1 - 25 2 27 0 0 26 - 50 51 - 100 101 - 250 Total I hope that helps.. -- Sue "Bob Phillips" wrote: Your question is not clear, at least to me. I am not seeing where the variable data is and hat is repeated, so what needs to be counted/summed? -- __________________________________ HTH Bob "Sue" wrote in message ... Question: Category Processed (Yes) Not Processed(No) Count Sum(Amt) Count Sum(Amt) 1 - 25 26 - 50 51 - 100 101 - 250 Total Data has two columns which are Amount and Status(Yes/No). Can someone help me with the formulae to count the number of coumns which fall in each Category and also sum of the amounts in those categories? -- Sue |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count and Sum functions with 2 criterias
With your data in A1:B9, try:
1-25: Processed (Yes) formula: =SUMPRODUCT(($A$2:$A$9<=25)*($B$2:$B$9="Yes")) Processed (Yes) Sum(Amt) formula: =SUMPRODUCT(($A$2:$A$9<=25)*($B$2:$B$9="Yes")*$A$2 :$A$9) Processed (No) formula: =SUMPRODUCT(($A$2:$A$9<=25)*($B$2:$B$9="No")) Processed (No) Sum(Amt) formula: =SUMPRODUCT(($A$2:$A$9<=25)*($B$2:$B$9="No")*$A$2: $A$9) 26-50: Processed (Yes) formula: =SUMPRODUCT(($A$2:$A$925)*($A$2:$A$9<=50)*($B$2:$ B$9="Yes")) Processed (Yes) Sum(Amt) formula: =SUMPRODUCT(($A$2:$A$925)*($A$2:$A$9<=50)*($B$2:$ B$9="Yes")*$A$2:$A$9) Processed (No) formula: =SUMPRODUCT(($A$2:$A$925)*($A$2:$A$9<=50)*($B$2:$ B$9="No")) Processed (No) Sum(Amt) formula: =SUMPRODUCT(($A$2:$A$925)*($A$2:$A$9<=50)*($B$2:$ B$9="No")*$A$2:$A$9) Etc. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sue" wrote in message ... Let me give an example, Say the Data looks like this: Category Processed 2 Yes 50 No 40 Yes 25 Yes 90 No 7102 Yes 198 Yes 648 No The Format that I would like is as follows: Category Processed (Yes) Not Processed(No) Count Sum(Amt) Count Sum(Amt) 1 - 25 2 27 0 0 26 - 50 51 - 100 101 - 250 Total I hope that helps.. -- Sue "Bob Phillips" wrote: Your question is not clear, at least to me. I am not seeing where the variable data is and hat is repeated, so what needs to be counted/summed? -- __________________________________ HTH Bob "Sue" wrote in message ... Question: Category Processed (Yes) Not Processed(No) Count Sum(Amt) Count Sum(Amt) 1 - 25 26 - 50 51 - 100 101 - 250 Total Data has two columns which are Amount and Status(Yes/No). Can someone help me with the formulae to count the number of coumns which fall in each Category and also sum of the amounts in those categories? -- Sue |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count and Sum functions with 2 criterias
If you put your category range in two columns like this:
1 25 26 50 51 100 101 250 you could then simplify the formulae that Sandy gave you by refering to the cells containing the range rather than include them explicitly in the formulae. Hope this helps. Pete On Oct 7, 10:44*am, Sue wrote: Let me give an example, Say the Data looks like this: Category * * * *Processed 2 * * * Yes 50 * * *No 40 * * *Yes 25 * * *Yes 90 * * *No 7102 * *Yes 198 * * Yes 648 * * No The Format that I would like is as follows: Category * * * * * *Processed (Yes) * * * * * * * * * * * * * * * Not Processed(No) * * * * * * * * * * * * Count * * * Sum(Amt) * * * * * * * * * * * Count * * *Sum(Amt) 1 - 25 * * * * * * * * *2 * * * * * * * 27 * * * * * * * * * * * * * * * *0 * * * * * * * *0 26 - 50 * * * * * * * 51 - 100 101 - 250 Total I hope that helps.. -- Sue "Bob Phillips" wrote: Your question is not clear, at least to me. I am not seeing where the variable data is and hat is repeated, so what needs to be counted/summed? -- __________________________________ HTH Bob "Sue" wrote in message ... Question: Category * * * * *Processed (Yes) * * * Not Processed(No) * * * * Count * * * Sum(Amt) * * * * * * *Count * * *Sum(Amt) 1 - 25 26 - 50 51 - 100 101 - 250 Total Data has two columns which are Amount and Status(Yes/No). Can someone help me with the formulae to count the number of coumns which fall in each Category and also sum of the amounts in those categories? -- Sue- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count and Sum functions with 2 criterias
Thank you so much.. I tried it with the Absolute values and it works.. Will
have to try the way Pete wrote as well.. thanks both of you.. -- Sue "Sandy Mann" wrote: Very true Pete, I also forgot to point out that I used Absolute ranges so that the formula could be dragged down. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Pete_UK" wrote in message ... If you put your category range in two columns like this: 1 25 26 50 51 100 101 250 you could then simplify the formulae that Sandy gave you by refering to the cells containing the range rather than include them explicitly in the formulae. Hope this helps. Pete On Oct 7, 10:44 am, Sue wrote: Let me give an example, Say the Data looks like this: Category Processed 2 Yes 50 No 40 Yes 25 Yes 90 No 7102 Yes 198 Yes 648 No The Format that I would like is as follows: Category Processed (Yes) Not Processed(No) Count Sum(Amt) Count Sum(Amt) 1 - 25 2 27 0 0 26 - 50 51 - 100 101 - 250 Total I hope that helps.. -- Sue "Bob Phillips" wrote: Your question is not clear, at least to me. I am not seeing where the variable data is and hat is repeated, so what needs to be counted/summed? -- __________________________________ HTH Bob "Sue" wrote in message ... Question: Category Processed (Yes) Not Processed(No) Count Sum(Amt) Count Sum(Amt) 1 - 25 26 - 50 51 - 100 101 - 250 Total Data has two columns which are Amount and Status(Yes/No). Can someone help me with the formulae to count the number of coumns which fall in each Category and also sum of the amounts in those categories? -- Sue- Hide quoted text - - Show quoted text - |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count and Sum functions with 2 criterias
The formulae works for Processed counting but
Processed (Yes) Sum(Amt) formula: =SUMPRODUCT(($A$2:$A$925)*($A$2:$A$9<=50)*($B$2:$ B$9="Yes")*$A$2:$A$9) is giving me a Zero as the result. Am i doing something wrong? -- Sue "Sandy Mann" wrote: Very true Pete, I also forgot to point out that I used Absolute ranges so that the formula could be dragged down. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Pete_UK" wrote in message ... If you put your category range in two columns like this: 1 25 26 50 51 100 101 250 you could then simplify the formulae that Sandy gave you by refering to the cells containing the range rather than include them explicitly in the formulae. Hope this helps. Pete On Oct 7, 10:44 am, Sue wrote: Let me give an example, Say the Data looks like this: Category Processed 2 Yes 50 No 40 Yes 25 Yes 90 No 7102 Yes 198 Yes 648 No The Format that I would like is as follows: Category Processed (Yes) Not Processed(No) Count Sum(Amt) Count Sum(Amt) 1 - 25 2 27 0 0 26 - 50 51 - 100 101 - 250 Total I hope that helps.. -- Sue "Bob Phillips" wrote: Your question is not clear, at least to me. I am not seeing where the variable data is and hat is repeated, so what needs to be counted/summed? -- __________________________________ HTH Bob "Sue" wrote in message ... Question: Category Processed (Yes) Not Processed(No) Count Sum(Amt) Count Sum(Amt) 1 - 25 26 - 50 51 - 100 101 - 250 Total Data has two columns which are Amount and Status(Yes/No). Can someone help me with the formulae to count the number of coumns which fall in each Category and also sum of the amounts in those categories? -- Sue- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count and Sum functions with 2 criterias
Hi Sandy, pls ignore my previous message, it works now. Must have had some
cell ref wrong.. -- Sue "Sandy Mann" wrote: Very true Pete, I also forgot to point out that I used Absolute ranges so that the formula could be dragged down. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Pete_UK" wrote in message ... If you put your category range in two columns like this: 1 25 26 50 51 100 101 250 you could then simplify the formulae that Sandy gave you by refering to the cells containing the range rather than include them explicitly in the formulae. Hope this helps. Pete On Oct 7, 10:44 am, Sue wrote: Let me give an example, Say the Data looks like this: Category Processed 2 Yes 50 No 40 Yes 25 Yes 90 No 7102 Yes 198 Yes 648 No The Format that I would like is as follows: Category Processed (Yes) Not Processed(No) Count Sum(Amt) Count Sum(Amt) 1 - 25 2 27 0 0 26 - 50 51 - 100 101 - 250 Total I hope that helps.. -- Sue "Bob Phillips" wrote: Your question is not clear, at least to me. I am not seeing where the variable data is and hat is repeated, so what needs to be counted/summed? -- __________________________________ HTH Bob "Sue" wrote in message ... Question: Category Processed (Yes) Not Processed(No) Count Sum(Amt) Count Sum(Amt) 1 - 25 26 - 50 51 - 100 101 - 250 Total Data has two columns which are Amount and Status(Yes/No). Can someone help me with the formulae to count the number of coumns which fall in each Category and also sum of the amounts in those categories? -- Sue- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count for multiple text criterias | Excel Worksheet Functions | |||
| Count with multiple criterias | Excel Worksheet Functions | |||
| functions to count Yes & No | Excel Worksheet Functions | |||
| "Count If" 3 criterias are fulfilled | Excel Discussion (Misc queries) | |||
| Formula format for Count or Countif funtion with two criterias | Excel Worksheet Functions | |||
Hybrid Mode
