Hello, can someone help me figure this out? Essentially I am counting based on new data from the prior day.

However, when it is a Monday I want the count to include Friday, Saturday and Sunday's data. I also need

to accomplish the same thing using an autofilter (see current code at end of this request).This is my current formula that I need to modify when it is a Monday::

=COUNTIFS(Pipeline!$AH$7:$AH$999,"<>*Pre-Approval*",Pipeline!$FF$7:$FF$999,"",Pipeline!$F$7:$F$999,">1", Pipeline!$U$7:$U$999,TODAY()-1)I found this formula but this just reverts back to Friday:

=IF(WEEKDAY(B5)=2,B5-3,B5)autofilter code I currently have that I need to modify when it is a Monday :

txtDate = Date - 1 ActiveSheet.Range("$a$6:$FG$1000").AutoFilter Field:=21, Criteria1:=txtDateANy help would be greatly appreciated, thank you

A quick and dirty solution might be to SUM three COUNTIFS. Something like this: =IF(WEEKDAY(B5)=2,

SUM(COUNTIFS(...,TODAY()-1),COUNTIFS(...,TODAY()-2),COUNTIFS(...,TODAY()-3)),

COUNTIFS(...,TODAY()-1))Perhaps you could put the COUNTIFS for TODAY()-1, -2 & -3 in their own Named cells so that the IF formula isn't as long as it would need to be if it was a single formula.

e.g.

=IF(WEEKDAY(B5)=2, SUM(Minus1, Minus2, Minus3), Minus1)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Thank you DerbyDad03 that worked like a charm, here is the final formula for anyone else searching: =IF(WEEKDAY(TODAY())=2,SUM(COUNTIFS(Pipeline!$AH$7:$AH$999,"<>*Pre-Approval*",Pipeline!$FF$7:$FF$999,"",Pipeline!$F$7:$F$999,">1",Pipeline!$U$7:$U$999,TODAY()-1),COUNTIFS(Pipeline!$AH$7:$AH$999,"<>*Pre-Approval*",Pipeline!$FF$7:$FF$999,"",Pipeline!$F$7:$F$999,">1",Pipeline!$U$7:$U$999,TODAY()-2),COUNTIFS(Pipeline!$AH$7:$AH$999,"<>*Pre-Approval*",Pipeline!$FF$7:$FF$999,"",Pipeline!$F$7:$F$999,">1",Pipeline!$U$7:$U$999,TODAY()-3)))Do you have a suggestion on how to make my autofilter show me that same data when the day

I am requesting is a Monday? here is my current autofilter which works Tuesday through Friday:

txtDate = Date - 1

ActiveSheet.Range("$a$6:$FG$1000").AutoFilter Field:=21, Criteria1:=txtDate

Please don't marked the thread as Solved if all of your questions haven't been answered. Try this:

txtDate1 = Date - 1 txtDate2 = Date - 2 txtDate3 = Date - 3 If Weekday(Date) <> 2 Then ActiveSheet.Range("$a$6:$FG$1000").AutoFilter Field:=21, Criteria1:=txtDate1 Else ActiveSheet.Range("$a$6:$FG$1000").AutoFilter Field:=21, _ Criteria1:=Array(txtDate1, txtDate2, txtDate3), Operator:=xlFilterValues End If

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Ask Your Question

Weekly Poll