# 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

#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)message edited by DerbyDad03

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 - 1ActiveSheet.Range("\$a\$6:\$FG\$1000").AutoFilter Field:=21, Criteria1:=txtDate

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```message edited by DerbyDad03

Report •
Related Solutions