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?

Nov 26, 2022 in Others 505 views

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))`
• 63,720 points

Is there a way to hide excel formula using Apache POI

In Excel, hiding formulae is a feature ...READ MORE

Using Excel Proper Function with exception | Excel

To accomplish this, you might need to ...READ MORE

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE