COUNTIFS formula using dates excluding weekend days

JEarley02
JEarley02 ✭✭✭
edited 12/09/19 in Formulas and Functions

I'm trying to figure out the correct formula for counting up the # of entries for a particular location with a "YES" or "NO" data entry for the previous date, TODAY (-1), that doesn't include weekends. 

For instance, if today=Monday, then the formula would need to search for the previous Friday date and count the # of entries for that location on that Friday date that have a "YES" or "NO" data entry. 

I have a need to see the trend for each of the past 7 days (in separate columns) for the total # of entries for each location.  I've figured out how to do this for today's data using TODAY(0)  using a COUNTIFS formula (see below.)  How can I add the second column for the previous day such that it will perform a similar count, but taking into account if TODAY(-1) is a Sunday then it will instead count the # of entries for the Friday date (there are no date entries in the file with a Sat or Sun date, so if I use TODAY(-1), it will calculate 0 for the previous date which is not correct given my data file).  Similarly, if TODAY(-2) is a Sunday or Saturday, then it will instead count the # of entries for the Friday date, and similar calculation for the -3, -4, -5, -6.

Basically, I want to see the # of entries for each date by location in 8 columns such that the dates correspond to the following columns - Today, Yesterday, The day before Yesterday, Two days before Yesterday, Three days before yesterday, Four days before yesterday, Five days before yesterday, Six days before yesterday.  

Here is my COUNTIFS formula for the Today column which works:  

=COUNTIFS({reference sheet date column}, =TODAY(0), {reference sheet location column}, "location name", {reference sheet data column}, OR(@cell = "YES", @cell = "NO"))   

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!