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 61 views

## 1 answer to this question.

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))`
• 51,600 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