Countifs formula using weekday function

0 votes

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 by Kithuzzz
• 38,000 points
626 views

1 answer to this question.

0 votes

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))
answered Nov 26, 2022 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
1,084 views
0 votes
1 answer
0 votes
1 answer

Is there a way to hide excel formula using Apache POI

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

answered Oct 11, 2022 in Others by narikkadan
• 63,600 points
1,244 views
0 votes
1 answer

Using Excel Proper Function with exception | Excel

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

answered Oct 22, 2022 in Others by narikkadan
• 63,600 points
521 views
0 votes
1 answer

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

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
4,102 views
0 votes
1 answer

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

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

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
2,009 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

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

Feb 17, 2022 in Others by Edureka
• 13,690 points
988 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

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

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
3,712 views
0 votes
1 answer

Is there a way to test a formula result in excel and type it only once, all within one cell and not using a user defined function?

Use the Let function: =LET(Value,A1+B2+C4+G3+B4,IF(Value>10,"No",Value)) I hope this helps ...READ MORE

answered Jan 9, 2023 in Others by narikkadan
• 63,600 points
679 views
0 votes
1 answer

Using Excel VLOOKUP() function across two sheets

The syntax for VLOOKUP is VLOOKUP(Lookup_Value,Table Array,Col_index_num,Range_lookup) OR, to start in ...READ MORE

answered Sep 30, 2022 in Others by narikkadan
• 63,600 points
1,001 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP