COUNTIFS with weekday and timeframe

[glitchone]

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

[#2]

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

[#3]

@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?

[#4]

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

[#5]

@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:

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"?

[#6]

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

[#7]

@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.

[#8 · Accepted 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.

[#9]

Thank you so so much!

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

Thanks also to @Patrick2788