Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count based on 2 conditions
I am using Excel 2003. Column B is the name of the account; column C is a
percentage for the month; column D returns the rank based on the percentage in column c. Subsequent columns have the following month for a percentage for that month and the next column is the rank Column B Column C Column D Column E Column F Jan Percent Rank Feb Percent Rank Account 1 85% 1 78% 2 Account 2 80% 2 82% 1 There are columns for 12 months of percentages and the corresponding columns for the rank. A formula is needed to count the number of times an account has a number 1 rank throughout the year, but not count those columns of months that have not occured as existing formulas return a rank of 1 for each of the accounts when the percentage is zero for the months that have no percentage. Thank you. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count based on 2 conditions
=IF(AND(B20,C2=1),1,0)+IF(AND(D20,E2=1),1,0)+IF( AND(F20,G2=1),1,0)+IF(AND(H20,H2=1),1,0)+IF(AND( J20,K2=1),1,0) etc, etc.
-- Alan Moseley IT Consultancy http://www.amitc.co.uk If I have solved your problem, please click Yes below. Thanks. "Basenji" wrote: I am using Excel 2003. Column B is the name of the account; column C is a percentage for the month; column D returns the rank based on the percentage in column c. Subsequent columns have the following month for a percentage for that month and the next column is the rank Column B Column C Column D Column E Column F Jan Percent Rank Feb Percent Rank Account 1 85% 1 78% 2 Account 2 80% 2 82% 1 There are columns for 12 months of percentages and the corresponding columns for the rank. A formula is needed to count the number of times an account has a number 1 rank throughout the year, but not count those columns of months that have not occured as existing formulas return a rank of 1 for each of the accounts when the percentage is zero for the months that have no percentage. Thank you. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count based on 2 conditions
There are 2 ways of achieving this:
1) Error trap the Ranks of 1 in months that haven't happened yet as this will remove the need to include this exclusion as a count condition. This ould be done, by something like: =if(A1TODAY(),"",RANK(b2,c2:c12,0) This checks whether your month (date in cell A1) is greater than the current date and if it is will insert an empty string, otherwise return a rank. If you do this, you can then just use a simple COUNTIF to count the ranks of 1 2) Use a 2 condition count, with SUMPRODUCT,i.e. =SUMPRODUCT(--(B1:B12=1),--(A1:A12<=TODAY())) which counts the number of ranks of 1 in range A1:A12 but only for months where the months/dates in A1:A12 is less than or equal today (happened). This presupposes you have your month as a full date i.e. dd/mm/yyyy, even if it is displated as mmm-yy. Jason -- Basenji wrote: I am using Excel 2003. Column B is the name of the account; column C is a percentage for the month; column D returns the rank based on the percentage in column c. Subsequent columns have the following month for a percentage for that month and the next column is the rank Column B Column C Column D Column E Column F Jan Percent Rank Feb Percent Rank Account 1 85% 1 78% 2 Account 2 80% 2 82% 1 There are columns for 12 months of percentages and the corresponding columns for the rank. A formula is needed to count the number of times an account has a number 1 rank throughout the year, but not count those columns of months that have not occured as existing formulas return a rank of 1 for each of the accounts when the percentage is zero for the months that have no percentage. Thank you. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count based on 2 conditions
There are 2 ways of achieving this:
1) 'Empty string' trap the Ranks of 1 in months that haven't happened yet as this will remove the need to include this exclusion as a count condition. This could be done, by something like: =if(A1TODAY(),"",RANK(b2,c2:c12,0) Use something like the above in all your RANK forumlas. This checks whether your month (date in cell A1) is greater than the current date and if it is will insert an empty string, otherwise return a rank. If you do this, you can then just use a simple COUNTIF to count the ranks of 1, with no need to worry about months that haven't happened yet. 2) Use a 2 condition count, with SUMPRODUCT,i.e. =SUMPRODUCT(--(B1:B12=1),--(A1:A12<=TODAY())) which counts the number of ranks of 1 in range B1:B12 but only for months where the months/dates in A1:A12 is less than or equal today (happened). This presupposes you have your month stored as a full date i.e. dd/mm/yyyy, even if it is displated as mmm-yy. Jason -- Basenji wrote: I am using Excel 2003. Column B is the name of the account; column C is a percentage for the month; column D returns the rank based on the percentage in column c. Subsequent columns have the following month for a percentage for that month and the next column is the rank Column B Column C Column D Column E Column F Jan Percent Rank Feb Percent Rank Account 1 85% 1 78% 2 Account 2 80% 2 82% 1 There are columns for 12 months of percentages and the corresponding columns for the rank. A formula is needed to count the number of times an account has a number 1 rank throughout the year, but not count those columns of months that have not occured as existing formulas return a rank of 1 for each of the accounts when the percentage is zero for the months that have no percentage. Thank you. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count based on 2 conditions
Thank you. It accomplished what needed to be counted.
"Basenji" wrote: I am using Excel 2003. Column B is the name of the account; column C is a percentage for the month; column D returns the rank based on the percentage in column c. Subsequent columns have the following month for a percentage for that month and the next column is the rank Column B Column C Column D Column E Column F Jan Percent Rank Feb Percent Rank Account 1 85% 1 78% 2 Account 2 80% 2 82% 1 There are columns for 12 months of percentages and the corresponding columns for the rank. A formula is needed to count the number of times an account has a number 1 rank throughout the year, but not count those columns of months that have not occured as existing formulas return a rank of 1 for each of the accounts when the percentage is zero for the months that have no percentage. Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count based on 2 conditions by using vb | Excel Discussion (Misc queries) | |||
Count based on Conditions | Excel Worksheet Functions | |||
Array - Count based on 3 conditions | Excel Discussion (Misc queries) | |||
Count based on multiple conditions | Excel Discussion (Misc queries) | |||
How do I count based on two conditions? | New Users to Excel |