Jaimee001
Member
Hi Team,
Back in September, I posted a request looking where the daily count was less than 20 a day by account:
I have a report with about 500 accounts ( column A). Column B is a date column from 1/1/2015 through 5/31/2015. Column C is their total daily count for that day. Column D is a formula to determine if the daily count is >(greater than) 20 and Column E is a formula to determine the daily count is <(less than) 20.
I need to extract or highlight those accounts where their daily count are less than 20 for their date range only. Not any account where they may have both greater and less than.
Haseeb A responded with this formula:
IF(AND(C2<21,COUNTIFS(A$2:A$20666,A2,C$2:C$20666,">20")=0),"<20","")
and is worked perfectly!
Now I need to have 2 columns:
one where the count is between 11-20 and the 2nd where the count is less than 11
I tried to create a 2nd formula for the less than 11
IF(AND(C2<11,COUNTIFS(A$2:A$17025,A2,D$2:D$17025,">9")=0),"<10","")
but it's not working....
Can it be done in 1 formula?
I'm attaching a V2 of the original reply from Haseeb...
I did review those files that Hui sent.
Thank you all in advance...
Back in September, I posted a request looking where the daily count was less than 20 a day by account:
I have a report with about 500 accounts ( column A). Column B is a date column from 1/1/2015 through 5/31/2015. Column C is their total daily count for that day. Column D is a formula to determine if the daily count is >(greater than) 20 and Column E is a formula to determine the daily count is <(less than) 20.
I need to extract or highlight those accounts where their daily count are less than 20 for their date range only. Not any account where they may have both greater and less than.
Haseeb A responded with this formula:
IF(AND(C2<21,COUNTIFS(A$2:A$20666,A2,C$2:C$20666,">20")=0),"<20","")
and is worked perfectly!
Now I need to have 2 columns:
one where the count is between 11-20 and the 2nd where the count is less than 11
I tried to create a 2nd formula for the less than 11
IF(AND(C2<11,COUNTIFS(A$2:A$17025,A2,D$2:D$17025,">9")=0),"<10","")
but it's not working....
Can it be done in 1 formula?
I'm attaching a V2 of the original reply from Haseeb...
I did review those files that Hui sent.
Thank you all in advance...