incorporating day of week in COUNTIFS

June 19, 2017 at 05:08:40
Specs: Windows 7
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:=txtDate

ANy help would be greatly appreciated, thank you


See More: incorporating day of week in COUNTIFS

Reply ↓  Report •

#1
June 19, 2017 at 06:13:58
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


Reply ↓  Report •

#2
June 19, 2017 at 14:28:07
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


Reply ↓  Report •

#3
June 19, 2017 at 17:44:13
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


Reply ↓  Report •
Related Solutions


Ask Question