Saturday, January 24, 2015

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.
 
 
Thanks in advance
--------------------------------------------------------------------------------------------------------------------------------------



No comments:

Post a Comment