SOLVED

COUNTIFS with weekday and timeframe

New Contributor

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:

ABCDEFGHI
Cashier nametransaction no.Date First nameLast nameWeekdayTimeRevenue
XY12319.08.2022Mr.XXYYFreitag14:02:54122,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

@glitchone 

 

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

@Hans Vogelaar 

 

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?

@glitchone 

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

@Hans Vogelaar 

 

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:

ABCDEFGHIJKLM
KassennameBelegnummerDatumVornameAnredeNameStatusWochentagZeitBetragVerkäuferKundennummerKassenbuchnr.
VinothekKA2200083019.08.2022  BarverkaufGebuchtFreitag15:52:5319,50Vinothek1000127587

 

any clues why it tells "0"?

@glitchone 

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

 

@glitchone 

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

@Hans Vogelaar 

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.

Thank you so so much!

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

Thanks also to @Patrick2788