how to extract accounts that have a specific daily count less then 20

[Jaimee001]

Hi Team,
I've been racking my brain trying to figure out how to do this. 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.
I tried to do it in a pivot table but I'm not sure how to do that.

I have a mock up report attached. The 2 accounts highlighted in yellow are the ones that have daily counts of < 20.

Thank you in advance...

Jaimee

 

[Haseeb A]

Hello Jaime,

Since you have already using a helper column, change E2 formula to,

=IF(AND(C2<21,COUNTIFS(A$2:A$108,A2,C$2:C$108,">20")=0),"<20","")

Then use PT as showing in attached.

Hope this helps you Jaime.

 

[Hui]

Jaime

You can use the techniques described here to run a report

Single Criteria
http://chandoo.org/wp/2011/11/18/formula-forensics-003/

Multiple Criteriahttp://chandoo.org/wp/2014/11/10/formula-forensics-no-003b-lukes-reward-part-ii/