Count based on 2 conditions

[Basenji]

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.

 

[Alan Moseley]

=IF(AND(B2>0,C2=1),1,0)+IF(AND(D2>0,E2=1),1,0)+IF(AND(F2>0,G2=1),1,0)+IF(AND(H2>0,H2=1),1,0)+IF(AND(J2>0,K2=1),1,0) etc, etc.

 

[Jason]

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(A1>TODAY(),"",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

 

[Jason]

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(A1>TODAY(),"",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]

Thank you. It accomplished what needed to be counted.