COUNTIFS formula using dates excluding weekend days

JEarley02

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"))   

Paul Newcome

Try setting up a table similar to below (column headers are in bold)...

Weekday      Adjustment

       1                  -2

       2                    0

       3                    0

       4                    0

       5                    0

       6                    0

       7                    -1

 

You can then use the following formula (change column names and x-sheet references as necessary)

 

=TODAY() + INDEX(Adjustment1:Adjustment7, MATCH(WEEKDAY(TODAY()), Weekday1:Weekday7, 0))

 

What this will do is look at the weekday for today (or yesterday or the day before etc based on the number you have in the TODAY function). If that weekday is a Sunday, it will add a -2 which will put it at the Friday before. Monday through Friday will not adjust the date as they are weekdays (2 - 6 in the table), and if the weekday is a Saturday (7 in the table above) it will subtract 1 to put it at the Friday before.

 

This will give you the date for TODAY(), TODAY(-1), etc. to be used in your COUNTIFS formula. From there you can either have this formula as a standalone cell and reference the cell in your formula, or you can replace the =TODAY(#) section of your formula with the formula above.

JEarley02

Thank you.  I'm trying the suggestion and getting "unparseable" as the response and not able to figure out what may be wrong.  Here is my formula in the destination sheet including the suggestion using -1 for the previous day:

=COUNTIFS({Lab MA Leadership Standard Work - MAIN Date}, =TODAY(-1), + INDEX(Adjustment1:Adjustment7, MATCH(WEEKDAY(TODAY(-1)), Weekday1:Weekday7, 0))), {Lab MA Leadership Standard Work - MAIN Clinic Name}, "ALT", {Lab MA Leadership Standard Work - MAIN PT ID}, OR(@cell = "YES", @cell = "NO"))

Can you spot what I may be doing incorrectly in the formula?  I am attempting to count the # of entries with a yes or no for location "ALT"  on the previous day in the reference sheet, and taking into account if it's a Saturday or Sunday to use the Friday date to perform the count.  I set up the 2 columns (Adjustment and Weekday) as suggested in the destination sheet where the count value's from the reference sheet will be saved.

Paul Newcome

Try removing the comma between TODAY(-1) and INDEX so it reads as 

 

TODAY(-1) + INDEX

 

instead of 

 

TODAY(-1), + INDEX

JEarley02

Thank you, again!  I sincerely appreciate your help.  I've tried removing the comma and it still remains as "unparseable".  Sigh....  I feel like I'm so close to having this....do you have any other suggestions?   

Paul Newcome

You have an extra parenthesis at the end of your INDEX/MATCH.

 

=COUNTIFS(................................Weekday1:Weekday7, 0)), {Lab MA Leadership....................)

 

You should only have two there, but you have three.

JEarley02

Hah, I thought so too and already tried that - there are 6 left parenthesis and 6 right parenthesis. I believe I have figured out the problem, it was the "=" in front of TODAY.  Here is the formula that seems to now be working:

=COUNTIFS({Lab MA Leadership Standard Work - MAIN Date}, TODAY(-1) + INDEX(Adjustment$1:Adjustment$7, MATCH(WEEKDAY(TODAY(-1)), Weekday$1:Weekday$7, 0)), {Lab MA Leadership Standard Work - MAIN Clinic Name}, "ALT", {Lab MA Leadership Standard Work - MAIN PT ID}, OR(@cell = "YES", @cell = "NO"))

Paul Newcome

If you count the parenthesis out, you will see that you are closing the COUNTIFS early. Removing the extra parenthesis in the middle of the formula is necessary. If that still does not work then there is an issue elsewhere.

 

=COUNTIFS(1 open......................, =TODAY(2 open......) 1 open, + INDEX(2 open.............., MATCH(3 open......WEEKDAY(4 open......TODAY(5 open......)4 open.........)3 open........, Weekday1:Weekday7, 0)2 open..........)1 open..........) 0 open......., {Lab MA Leadership Standard Work - MAIN Clinic Name}, "ALT", {Lab MA Leadership Standard Work - MAIN PT ID}, OR(1 open.........."YES", @cell = "NO")0 open.......) -1 open...

 

Below is the formula corrected...

 

=COUNTIFS({Lab MA Leadership Standard Work - MAIN Date}, =TODAY(-1), + INDEX(Adjustment1:Adjustment7, MATCH(WEEKDAY(TODAY(-1)), Weekday1:Weekday7, 0)), {Lab MA Leadership Standard Work - MAIN Clinic Name}, "ALT", {Lab MA Leadership Standard Work - MAIN PT ID}, OR(@cell = "YES", @cell = "NO"))

 

 

JEarley02

Yes, there was an extra one.  Thank you.    I found by removing the "=" in front of TODAY(-1), it seems to have solved the problem.  I will be able to validate the total when I get to the end and do a summation of all locations.

Paul Newcome

Happy to help. 

badman242

#No MATCH Error

 

Good morning Im Getting the following error M,T,W,T work Ok but  for some reason Friday it show NO MATCH

=COUNTIFS({DATE1}, TODAY(-1) + INDEX(Adjustment1:Adjustment7, MATCH(WEEKDAY(TODAY(-1)), Weekday1:Weekday7, 0)), {Type2}, "FE"

Andrée Starå

Hi,

Can you describe your process in more detail and maybe share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting