Forum Discussion
Solved
COUNTIFS with weekday and timeframe
Hi, i am currently working on a sheet which has to calculate a certain no. of customers and revenue in a specific daytime and on a specific weekday and need a little help: Specifically i want...
Thank you for sending the file by PM. The values in column I look like times, and they are formatted as times, but they are text values. You can do the following to convert them to real times:
Select column I.
On the Data tab of the ribbon, click Text to Columns.
Without changing any settings, click Finish.
I'll send the file back by PM.
Legacy solution with Ctrl+Shift+Enter array:
Count:
=COUNT(IF(WEEKDAY($E$3:$E$7,2)=5,IF($F$3:$F$7>=10/24,IF($F$3:$F$7<=0.5,$G$3:$G$7))))Sum of revenue:
=SUM(IF(WEEKDAY($E$3:$E$7,2)=5,IF($F$3:$F$7>=10/24,IF($F$3:$F$7<=0.5,$G$3:$G$7))))365 solution with FILTER:
count
=LET(d,E3:E7,t,F3:F7,f,FILTER(d,(WEEKDAY(d,2)=5)*(t>10/24)*(t<=0.5)),ROWS(f))sum of revenue
=LET(d,E3:E7,t,F3:F7,rev,G3:G7,f,FILTER(rev,(WEEKDAY(d,2)=5)*(t>10/24)*(t<=0.5)),SUM(f))