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 - |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count and Sum functions with 2 criterias
Are you saying that:
=SUMPRODUCT(($A$2:$A$925)*($A$2:$A$9<=50)*($B$2:$ B$9="Yes")) returns 1 but that: =SUMPRODUCT(($A$2:$A$925)*($A$2:$A$9<=50)*($B$2:$ B$9="Yes")*$A$2:$A$9) Returns zero? -- 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 ... 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 - |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count and Sum functions with 2 criterias
I'm glad about that because you had me scratching my head. Good to hear
that you got it sorted out. -- Regards, 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 ... 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 - |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count and Sum functions with 2 criterias
Hi Sandy,
Thanks for your help! I have another one for you, can you please help me with this one too? I have two worksheets, the first one (say Database 2) has 4 columns - ORDER NUMBER, CUSTOMER, BILL AMOUNT, BILL DATE The second one (say Database 3) has 4 columns - ORDER NUMBER, PAID/ REJECTED, PAID AMOUNT, PAID/REJECTED DATE I want to make a new one with the following information: For each Customer 1. Billed - Number of Orders, Total Billed Amount 2. Paid - Number of Orders, Total Billed Amount 3. Rejected - Number of Orders, Total Billed Amount 4. % Paid -- Sue "Sandy Mann" wrote: I'm glad about that because you had me scratching my head. Good to hear that you got it sorted out. -- Regards, 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 ... 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 - |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count and Sum functions with 2 criterias
The only difficulty is that fact that you do not appear to be using the
Order Numbers in the formula requirement and that is the only column common to both sheets. With the Columns Headers you give in Column A, B & C in both sheets and with a Customer's name in F2 of Database3 use the following formulas in Database 3: 1. Billed - Number of Orders: =COUNTIF('Database 2'!B2:B300,F2) Total Billed Amount: =SUMPRODUCT(('Database 2'!B2:B300=F2)*('Database 2'!C2:C300)) 2. Paid - Number of Orders: =SUMPRODUCT((('Database 2'!B2:B300=F2)*('Database 2'!A2:A300)= ('Database 3'!A2:A300))*('Database 3'!B2:B300="Paid")) Total Billed Amount: =SUMPRODUCT((('Database 2'!B2:B300=F2)*('Database 2'!A2:A300)= ('Database 3'!A2:A300))*('Database 3'!B2:B300="Paid")*'Database 3'!C2:C300) 3. Rejected - Number of Orders: =SUMPRODUCT((('Database 2'!B2:B300=F2)*('Database 2'!A2:A300)= ('Database 3'!A2:A300))*('Database 3'!B2:B300="Rejected")) Total Billed Amount: =SUMPRODUCT((('Database 2'!B2:B300=F2)*('Database 2'!A2:A300)= ('Database 3'!A2:A300))*('Database 3'!B2:B300="Rejected")* 'Database 3'!C2:C300) 4. % Paid: =<Billed Number of Oders above / <Paid Total Billed Amount above and format as Percentage. It is not necessary to reference the sheet that the formula is in but I have included them above for clarity. Without using the host sheet the formulas a 2. Paid - Number of Orders: =SUMPRODUCT((('Database 2'!B2:B300=F2)*('Database 2'!A2:A300)= (A2:A300))*(B2:B300="Paid")) Total Billed Amount: =SUMPRODUCT((('Database 2'!B2:B300=F2)*('Database 2'!A2:A300)= (A2:A300))*(B2:B300="Paid")*C2:C300) 3. Rejected - Number of Orders: =SUMPRODUCT((('Database 2'!B2:B300=F2)*('Database 2'!A2:A300)= (A2:A300))*(B2:B300="Rejected")) Total Billed Amount: =SUMPRODUCT((('Database 2'!B2:B300=F2)*('Database 2'!A2:A300)= (A2:A300))*(B2:B300="Rejected")*C2:C300) -- 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 ... Hi Sandy, Thanks for your help! I have another one for you, can you please help me with this one too? I have two worksheets, the first one (say Database 2) has 4 columns - ORDER NUMBER, CUSTOMER, BILL AMOUNT, BILL DATE The second one (say Database 3) has 4 columns - ORDER NUMBER, PAID/ REJECTED, PAID AMOUNT, PAID/REJECTED DATE I want to make a new one with the following information: For each Customer 1. Billed - Number of Orders, Total Billed Amount 2. Paid - Number of Orders, Total Billed Amount 3. Rejected - Number of Orders, Total Billed Amount 4. % Paid -- Sue "Sandy Mann" wrote: I'm glad about that because you had me scratching my head. Good to hear that you got it sorted out. -- Regards, 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 ... 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 - |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count and Sum functions with 2 criterias
One caveat to my previous suggestion, if you have more than one entry of the
*same* Order Number for any Customer then the formulas will return wrong results. It would be far better therefore to have the names of Customes in the Rows of Database 3 as well. -- 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 ... Hi Sandy, Thanks for your help! I have another one for you, can you please help me with this one too? I have two worksheets, the first one (say Database 2) has 4 columns - ORDER NUMBER, CUSTOMER, BILL AMOUNT, BILL DATE The second one (say Database 3) has 4 columns - ORDER NUMBER, PAID/ REJECTED, PAID AMOUNT, PAID/REJECTED DATE I want to make a new one with the following information: For each Customer 1. Billed - Number of Orders, Total Billed Amount 2. Paid - Number of Orders, Total Billed Amount 3. Rejected - Number of Orders, Total Billed Amount 4. % Paid -- Sue "Sandy Mann" wrote: I'm glad about that because you had me scratching my head. Good to hear that you got it sorted out. -- Regards, 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 ... 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 - |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count and Sum functions with 2 criterias
Sue,
Having slept on it I think that my suggestion was not a bad idea - it was a TERRIBLE idea. Not only does it have the failing listed previously but it also returns wrong results if the order of Order Numbers is not the same in both sheets. I would *strongly* recommend that you use a "Helper" column in Database 3. I used Column G but any column will do and if you want you can hide the "Helper" column. In G2 of Database 3 enter the formula: =IF(A2="","",VLOOKUP(A2,'Database 2'!$A$2:$B$30,2,FALSE)) and copy down as far as required. Then enter a list of Customer Names in Column H starting from H2 The "Billed" formulas are OK because they only reference Database 2 but change the other formulas as follows: In the same Row as the Billed Formulas enter: Paid - Number of Orders: =SUMPRODUCT(('Database 3'!$G$2:$G$300=H2)*('Database 3'!$B$2:$B$300="Paid")) Total Billed Amount: =SUMPRODUCT(('Database 3'!$G$2:$G$300=H2)*('Database 3'!$B$2:$B$300="Paid")*'Database 3'!$C$2:$C$300) Rejected - Number of Orders: =SUMPRODUCT(('Database 3'!$G$2:$G$300=H2)*('Database 3'!$B$2:$B$300="Rejected")) Total Billed Amount: =SUMPRODUCT(('Database 3'!$G$2:$G$300=H2)*('Database 3'!$B$2:$B$300="Rejected")*'Database 3'!$C$2:$C$300) The ranges are Absolute so that you can drag down on the fill handle for the other Customer Names in Column H. I have deliberately left the Formulas in Column G so that they will return a #N/A error if no match is found. This is because if it returned an empty string then the above formulas would ignore that entry and thus again return a wrong result. As it is the above formulas will echo the #N/A errors returned by Column G. My apologies if I have caused any confusion. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... One caveat to my previous suggestion, if you have more than one entry of the *same* Order Number for any Customer then the formulas will return wrong results. It would be far better therefore to have the names of Customes in the Rows of Database 3 as well. -- 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 ... Hi Sandy, Thanks for your help! I have another one for you, can you please help me with this one too? I have two worksheets, the first one (say Database 2) has 4 columns - ORDER NUMBER, CUSTOMER, BILL AMOUNT, BILL DATE The second one (say Database 3) has 4 columns - ORDER NUMBER, PAID/ REJECTED, PAID AMOUNT, PAID/REJECTED DATE I want to make a new one with the following information: For each Customer 1. Billed - Number of Orders, Total Billed Amount 2. Paid - Number of Orders, Total Billed Amount 3. Rejected - Number of Orders, Total Billed Amount 4. % Paid -- Sue "Sandy Mann" wrote: I'm glad about that because you had me scratching my head. Good to hear that you got it sorted out. -- Regards, 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 ... 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 - |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count and Sum functions with 2 criterias
Hi,
The Lookup formula did work and gave me the list of the customers matching that order number in column G. Each customer (say 10 customers) can have various order numbers(say 500 different order numbers). But when I tried the other formulae, the result for No. of orders is giving me a #NA and the billed amounts is giving a '0'. Not sure if im doing something wrong. -- Sue "Sandy Mann" wrote: Sue, Having slept on it I think that my suggestion was not a bad idea - it was a TERRIBLE idea. Not only does it have the failing listed previously but it also returns wrong results if the order of Order Numbers is not the same in both sheets. I would *strongly* recommend that you use a "Helper" column in Database 3. I used Column G but any column will do and if you want you can hide the "Helper" column. In G2 of Database 3 enter the formula: =IF(A2="","",VLOOKUP(A2,'Database 2'!$A$2:$B$30,2,FALSE)) and copy down as far as required. Then enter a list of Customer Names in Column H starting from H2 The "Billed" formulas are OK because they only reference Database 2 but change the other formulas as follows: In the same Row as the Billed Formulas enter: Paid - Number of Orders: =SUMPRODUCT(('Database 3'!$G$2:$G$300=H2)*('Database 3'!$B$2:$B$300="Paid")) Total Billed Amount: =SUMPRODUCT(('Database 3'!$G$2:$G$300=H2)*('Database 3'!$B$2:$B$300="Paid")*'Database 3'!$C$2:$C$300) Rejected - Number of Orders: =SUMPRODUCT(('Database 3'!$G$2:$G$300=H2)*('Database 3'!$B$2:$B$300="Rejected")) Total Billed Amount: =SUMPRODUCT(('Database 3'!$G$2:$G$300=H2)*('Database 3'!$B$2:$B$300="Rejected")*'Database 3'!$C$2:$C$300) The ranges are Absolute so that you can drag down on the fill handle for the other Customer Names in Column H. I have deliberately left the Formulas in Column G so that they will return a #N/A error if no match is found. This is because if it returned an empty string then the above formulas would ignore that entry and thus again return a wrong result. As it is the above formulas will echo the #N/A errors returned by Column G. My apologies if I have caused any confusion. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... One caveat to my previous suggestion, if you have more than one entry of the *same* Order Number for any Customer then the formulas will return wrong results. It would be far better therefore to have the names of Customes in the Rows of Database 3 as well. -- 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 ... Hi Sandy, Thanks for your help! I have another one for you, can you please help me with this one too? I have two worksheets, the first one (say Database 2) has 4 columns - ORDER NUMBER, CUSTOMER, BILL AMOUNT, BILL DATE The second one (say Database 3) has 4 columns - ORDER NUMBER, PAID/ REJECTED, PAID AMOUNT, PAID/REJECTED DATE I want to make a new one with the following information: For each Customer 1. Billed - Number of Orders, Total Billed Amount 2. Paid - Number of Orders, Total Billed Amount 3. Rejected - Number of Orders, Total Billed Amount 4. % Paid -- Sue "Sandy Mann" wrote: I'm glad about that because you had me scratching my head. Good to hear that you got it sorted out. -- Regards, 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 ... 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 - |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count and Sum functions with 2 criterias
"Sue" wrote in message
... But when I tried the other formulae, the result for No. of orders is giving me a #NA and the billed amounts is giving a '0'. Not sure if im doing something wrong. I'm not sure either. If I have any #N/A error returned in Column G I get #N/A errors all but the "Billed" formulas. Can you send me an example of the sheet, sanatised of sensitive data if necessary. Don't click on the *Reply* button because that will sent your reply to *maininator.com* which is a spam trap. Change the Mailinator.com to Tiscali.co.uk as it says in my signature. -- 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 ... Hi, The Lookup formula did work and gave me the list of the customers matching that order number in column G. Each customer (say 10 customers) can have various order numbers(say 500 different order numbers). But when I tried the other formulae, the result for No. of orders is giving me a #NA and the billed amounts is giving a '0'. Not sure if im doing something wrong. -- Sue "Sandy Mann" wrote: Sue, Having slept on it I think that my suggestion was not a bad idea - it was a TERRIBLE idea. Not only does it have the failing listed previously but it also returns wrong results if the order of Order Numbers is not the same in both sheets. I would *strongly* recommend that you use a "Helper" column in Database 3. I used Column G but any column will do and if you want you can hide the "Helper" column. In G2 of Database 3 enter the formula: =IF(A2="","",VLOOKUP(A2,'Database 2'!$A$2:$B$30,2,FALSE)) and copy down as far as required. Then enter a list of Customer Names in Column H starting from H2 The "Billed" formulas are OK because they only reference Database 2 but change the other formulas as follows: In the same Row as the Billed Formulas enter: Paid - Number of Orders: =SUMPRODUCT(('Database 3'!$G$2:$G$300=H2)*('Database 3'!$B$2:$B$300="Paid")) Total Billed Amount: =SUMPRODUCT(('Database 3'!$G$2:$G$300=H2)*('Database 3'!$B$2:$B$300="Paid")*'Database 3'!$C$2:$C$300) Rejected - Number of Orders: =SUMPRODUCT(('Database 3'!$G$2:$G$300=H2)*('Database 3'!$B$2:$B$300="Rejected")) Total Billed Amount: =SUMPRODUCT(('Database 3'!$G$2:$G$300=H2)*('Database 3'!$B$2:$B$300="Rejected")*'Database 3'!$C$2:$C$300) The ranges are Absolute so that you can drag down on the fill handle for the other Customer Names in Column H. I have deliberately left the Formulas in Column G so that they will return a #N/A error if no match is found. This is because if it returned an empty string then the above formulas would ignore that entry and thus again return a wrong result. As it is the above formulas will echo the #N/A errors returned by Column G. My apologies if I have caused any confusion. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... One caveat to my previous suggestion, if you have more than one entry of the *same* Order Number for any Customer then the formulas will return wrong results. It would be far better therefore to have the names of Customes in the Rows of Database 3 as well. -- 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 ... Hi Sandy, Thanks for your help! I have another one for you, can you please help me with this one too? I have two worksheets, the first one (say Database 2) has 4 columns - ORDER NUMBER, CUSTOMER, BILL AMOUNT, BILL DATE The second one (say Database 3) has 4 columns - ORDER NUMBER, PAID/ REJECTED, PAID AMOUNT, PAID/REJECTED DATE I want to make a new one with the following information: For each Customer 1. Billed - Number of Orders, Total Billed Amount 2. Paid - Number of Orders, Total Billed Amount 3. Rejected - Number of Orders, Total Billed Amount 4. % Paid -- Sue "Sandy Mann" wrote: I'm glad about that because you had me scratching my head. Good to hear that you got it sorted out. -- Regards, 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 ... 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 - |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count and Sum functions with 2 criterias
I sent the entire workbook to
"Sandy Mann" wrote: "Sue" wrote in message ... But when I tried the other formulae, the result for No. of orders is giving me a #NA and the billed amounts is giving a '0'. Not sure if im doing something wrong. I'm not sure either. If I have any #N/A error returned in Column G I get #N/A errors all but the "Billed" formulas. Can you send me an example of the sheet, sanatised of sensitive data if necessary. Don't click on the *Reply* button because that will sent your reply to *maininator.com* which is a spam trap. Change the Mailinator.com to Tiscali.co.uk as it says in my signature. -- 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 ... Hi, The Lookup formula did work and gave me the list of the customers matching that order number in column G. Each customer (say 10 customers) can have various order numbers(say 500 different order numbers). But when I tried the other formulae, the result for No. of orders is giving me a #NA and the billed amounts is giving a '0'. Not sure if im doing something wrong. -- Sue "Sandy Mann" wrote: Sue, Having slept on it I think that my suggestion was not a bad idea - it was a TERRIBLE idea. Not only does it have the failing listed previously but it also returns wrong results if the order of Order Numbers is not the same in both sheets. I would *strongly* recommend that you use a "Helper" column in Database 3. I used Column G but any column will do and if you want you can hide the "Helper" column. In G2 of Database 3 enter the formula: =IF(A2="","",VLOOKUP(A2,'Database 2'!$A$2:$B$30,2,FALSE)) and copy down as far as required. Then enter a list of Customer Names in Column H starting from H2 The "Billed" formulas are OK because they only reference Database 2 but change the other formulas as follows: In the same Row as the Billed Formulas enter: Paid - Number of Orders: =SUMPRODUCT(('Database 3'!$G$2:$G$300=H2)*('Database 3'!$B$2:$B$300="Paid")) Total Billed Amount: =SUMPRODUCT(('Database 3'!$G$2:$G$300=H2)*('Database 3'!$B$2:$B$300="Paid")*'Database 3'!$C$2:$C$300) Rejected - Number of Orders: =SUMPRODUCT(('Database 3'!$G$2:$G$300=H2)*('Database 3'!$B$2:$B$300="Rejected")) Total Billed Amount: =SUMPRODUCT(('Database 3'!$G$2:$G$300=H2)*('Database 3'!$B$2:$B$300="Rejected")*'Database 3'!$C$2:$C$300) The ranges are Absolute so that you can drag down on the fill handle for the other Customer Names in Column H. I have deliberately left the Formulas in Column G so that they will return a #N/A error if no match is found. This is because if it returned an empty string then the above formulas would ignore that entry and thus again return a wrong result. As it is the above formulas will echo the #N/A errors returned by Column G. My apologies if I have caused any confusion. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... One caveat to my previous suggestion, if you have more than one entry of the *same* Order Number for any Customer then the formulas will return wrong results. It would be far better therefore to have the names of Customes in the Rows of Database 3 as well. -- 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 ... Hi Sandy, Thanks for your help! I have another one for you, can you please help me with this one too? I have two worksheets, the first one (say Database 2) has 4 columns - ORDER NUMBER, CUSTOMER, BILL AMOUNT, BILL DATE The second one (say Database 3) has 4 columns - ORDER NUMBER, PAID/ REJECTED, PAID AMOUNT, PAID/REJECTED DATE I want to make a new one with the following information: For each Customer 1. Billed - Number of Orders, Total Billed Amount 2. Paid - Number of Orders, Total Billed Amount 3. Rejected - Number of Orders, Total Billed Amount 4. % Paid -- Sue "Sandy Mann" wrote: I'm glad about that because you had me scratching my head. Good to hear that you got it sorted out. -- Regards, 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 ... 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 - |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count and Sum functions with 2 criterias
Sue,
Sandy won't be very impressed that when he took the trouble to use a spam trap, *and* to point out that it was a spam trap and how to get to his real address, you then quoted his address in full in your message. The reason why he didn't quote the address in clear in the first place, and why he included the instructions as to how to change the address, is that it is easy for spammers to harvest addresses which are given in clear. -- David Biddulph "Sue" wrote in message ... I sent the entire workbook to [then Sue quoted Sandy's address in full] "Sandy Mann" wrote: "Sue" wrote in message ... But when I tried the other formulae, the result for No. of orders is giving me a #NA and the billed amounts is giving a '0'. Not sure if im doing something wrong. I'm not sure either. If I have any #N/A error returned in Column G I get #N/A errors all but the "Billed" formulas. Can you send me an example of the sheet, sanatised of sensitive data if necessary. Don't click on the *Reply* button because that will sent your reply to *maininator.com* which is a spam trap. Change the Mailinator.com to Tiscali.co.uk as it says in my signature. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk .... |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count and Sum functions with 2 criterias
i had no idea, im very sorry..
-- Sue "David Biddulph" wrote: Sue, Sandy won't be very impressed that when he took the trouble to use a spam trap, *and* to point out that it was a spam trap and how to get to his real address, you then quoted his address in full in your message. The reason why he didn't quote the address in clear in the first place, and why he included the instructions as to how to change the address, is that it is easy for spammers to harvest addresses which are given in clear. -- David Biddulph "Sue" wrote in message ... I sent the entire workbook to [then Sue quoted Sandy's address in full] "Sandy Mann" wrote: "Sue" wrote in message ... But when I tried the other formulae, the result for No. of orders is giving me a #NA and the billed amounts is giving a '0'. Not sure if im doing something wrong. I'm not sure either. If I have any #N/A error returned in Column G I get #N/A errors all but the "Billed" formulas. Can you send me an example of the sheet, sanatised of sensitive data if necessary. Don't click on the *Reply* button because that will sent your reply to *maininator.com* which is a spam trap. Change the Mailinator.com to Tiscali.co.uk as it says in my signature. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk .... |
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count and Sum functions with 2 criterias
"David Biddulph" <groups [at] biddulph.org.uk wrote in message
... Sue, Sandy won't be very impressed that when he took the trouble to use a spam trap, *and* to point out that it was a spam trap and how to get to his real address, you then quoted his address in full in your message. The reason why he didn't quote the address in clear in the first place, and why he included the instructions as to how to change the address, is that it is easy for spammers to harvest addresses which are given in clear. -- David Biddulph <Gracious smile from Sandy I will accept that it was my fault for not explicitly stating that my real address should not be posted in the NG's. My wife says that I expect people to know what I am thinking without me telling them and perhaps this is one of those occasions. Perhaps I should be using a text munged address like you David becauseother posters have simply pressed the *Reply* button. Mailinator.com only holds posts for "a few hours" then deletes them so there are no doubt that there are people out there that are thinking that I just ignored them. The reason I did not use a text munge to begin with was I thought that it was just a matter of time before the spammers programmed their bots to parse text addresses. -- Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk |
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 |