SOLVED

New Contributor

# 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

8 Replies

# Re: COUNTIFS with weekday and timeframe

=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))

# Re: COUNTIFS with weekday and timeframe

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?

# Re: COUNTIFS with weekday and timeframe

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))

# Re: COUNTIFS with weekday and timeframe

yea, i simplified the table to not fill up the post with unnecessary information.

the source table is called "Source" and is structured as followed:

 A B C D E F G H I J K L M Kassenname Belegnummer Datum Vorname Anrede Name Status Wochentag Zeit Betrag Verkäufer Kundennummer Kassenbuchnr. Vinothek KA22000830 19.08.2022 Barverkauf Gebucht Freitag 15:52:53 19,50 Vinothek 10001 27587

any clues why it tells "0"?

# Re: COUNTIFS with weekday and timeframe

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))``

# Re: COUNTIFS with weekday and timeframe

Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Alternatively, you can attach it to a private message to me. Thanks in advance.

best response confirmed by glitchone (New Contributor)
Solution

# Re: COUNTIFS with weekday and timeframe

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.

# Re: COUNTIFS with weekday and timeframe

Thank you so so much!

I didn't expect this to be reason why its not working!

Thanks also to @Patrick2788