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

How long do you think until flying cars or taxis are common?

Discuss in The Lounge

Poll History