Forum Discussion
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 to calculate:
- amount of customers on a friday in times between 10:00 AM and 12:00 PM
- total revenue on a friday in times between 10:00 AM and 12:00 PM
Source example:
| A | B | C | D | E | F | G | H | I |
| Cashier name | transaction no. | Date | First name | Last name | Weekday | Time | Revenue | |
| XY | 123 | 19.08.2022 | Mr. | XX | YY | Freitag | 14:02:54 | 122,00 |
Freitag is Friday in german btw.
In my understanding my function should look a little like this:
.=COUNTIFS(G2:G3000;"Freitag";I2:I3000;=>10:00:00;I2:I3000;=<12:00:00)
I think i may have an completely wrong approach.
I'd be grateful for any help offered.
Cheers
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.
8 Replies
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))=COUNTIFS(G2:G3000;"Freitag";I2:I3000;">="&TIME(10;0;0);I2:I3000;"<="&TIME(12;0;0))
=ZÄHLENWENNS(G2:G3000;"Freitag";I2:I3000;">="&ZEIT(10;0;0);I2:I3000;"<="&ZEIT(12;0;0))
Vielen Dank, leider gibt er mir nun eine "0" aus....
=ZÄHLENWENNS(Source!H2:H3155;"Freitag";Source!I2:I3155;">="&ZEIT(10;0;0);Source!I2:I3155;"<="&ZEIT(12;0;0))
Irgendetwas falsch?
In your first post, the day of the week was in column G and the time in column H so it should have been
=ZÄHLENWENNS(G2:G3000;"Freitag";H2:H3000;">="&ZEIT(10;0;0);H2:H3000;"<="&ZEIT(12;0;0))
