yogi_Count Number Of Items In aDataSheet That Are Older Than Specified Number Of Days Have Status As Specified And Are of Specific Color

             Google Spreadsheet   Post  #1885

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-23-2015

post by  Munkey (David):
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/AM2YVafG4Hc
Using COUNTIFS & DATEDIF without a helper column, anyone had any success with this

Hello all,

Hope we are all well?

 

Just trying to get my head around a problem, wondering if anyone else had solved it first.

Aim:

I have three columns,

Timestamp, Status and Type

Timestamp is a date and type.

Status - is either outstanding or completed

 

Type - In my example it's colours, only 4, but in reality it's a text string, but there are only a few types.

 

Basically, I wanted to try to count OUTSTANDING cases that are 28 days or older, by type.

The data is in aDataSheet

and would like the results in aReportSheet somewhere.

So for example, I'm trying to count
1) Cases 28 days or over

AND

2) are currently outstanding
AND

3) That are type "Red"

I've tried using COUNTIFS with DATEDIF and could never ever get it working, whatever I tried.
Have managed to get the results using FILTER and COUNT ( no helper column needed)

The formula is:

=Count(Filter(aDataSheet!A:C,aDataSheet!C:C=ʺRedʺ,aDataSheet!B:B=ʺOutstandingʺ,aDataSheet!A:A,datedif(aDataSheet!A:A,now(),ʺDʺ)>=28))

Ideally I think a countifs with datedif might be a little less intensive on the spreadsheet.
Want to avoid helper columns as they will bloat the sheet with data.

have shared an example sheet here.

 

https://docs.google.com/spreadsheets/d/11tB-o7kR2x6HzZnSm1o4OdU6N7rpX1jGAoz7JXLTQtE/edit#gid=969429723

 

Thanks in advance
--------------------------------------------------------------------------------------------------------------------------------------