Countifs formula using weekday function

Question

Based on the date in column AG, I have a formula I'm using for conditional formatting that highlights certain cells.

Logic:

If Today() is a Monday I need to highlight the cell if the date in AG is the PREVIOUS Friday, Saturday, and Sunday

If Today() is a Tuesday through Friday then I need to highlight if the date in AG is the previous day

The formula works great in the conditional formatting cells but I also would like to adapt it to a COUNTIFS formula so I can count the number of records that meet the criteria.

Here is the formula

: =OR(AND(WEEKDAY(TODAY())>= 3,WEEKDAY(TODAY())<=6,INT(AG10)=INT(TODAY()-1)),AND(WEEKDAY(TODAY())=2,INT(AG10)>=INT(TODAY()-3),INT(AG10)<=INT(TODAY()-1)))

Can someone help me tweak it to work with COUNTIF or COUNTIFS?

Answer 1

Use SUMPRODUCT:

=SUMPRODUCT(((WEEKDAY(TODAY())>= 3)*(WEEKDAY(TODAY())<=6)*(INT(AG10:AG100)=INT(TODAY()-1)))+((WEEKDAY(TODAY())=2)*(INT(AG10:AG100)>=INT(TODAY()-3))*(INT(AG10:AG100)<=INT(TODAY()-1))))

If the Dates in AG do not have a time component then we can use:

=IF(AND(WEEKDAY(TODAY())>= 3,WEEKDAY(TODAY())<=6),COUNTIF(AG:AG,TODAY()-1),IF(WEEKDAY(TODAY())=2,COUNTIFS(AG:AG,">=" & TODAY() -3,AG:AG,"<="&TODAY()-1),0))