• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to Extract Accounts that have a specific daily count version 2

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...
 

Attachments

  • How to filter on daily counts Results V2.xlsx
    361.3 KB · Views: 6
Back
Top