SUMIFS

[Ahmed Mostafa]

Hello All,

i am working in an hotel and i have been receiving requests for groups, like he wants 20 Rooms from 11-05 till 17-05.
so i was trying to come with one equation to distribute the rooms over the dates like seeing each date will have how many rooms .
so from 11-05 till 17-05 , that mean we have 20 rooms on (11,12,13,14,15,16) May .

i have tried the sum product function but it wasn't as i hoped , the function that will give the result i want will be the Sumifs , however i failed to make the criteria dynamic

Start Date	End Date	# Rooms	Date
11-05-24	13-05-24	10​	11-05-24
15-05-24	17-05-24	10​	12-05-24
12-05-24	13-05-24	10​	13-05-24
15-05-24	17-05-24	10​	14-05-24
22-05-24	23-05-24	10​	15-05-24
25-05-24	27-05-24	10​	16-05-24
26-05-24	27-05-24	10​	17-05-24
29-05-24	31-05-24	10​	18-05-24
29-05-24	30-05-24	40​	19-05-24
01-06-24	03-06-24	10​	20-05-24
02-06-24	03-06-24	30​	21-05-24
05-06-24	07-06-24	10​	22-05-24
05-06-24	06-06-24	20​	23-05-24
08-06-24	10-06-24	10​	24-05-24
09-06-24	10-06-24	15​	25-05-24
12-05-24	14-05-24	20​	26-05-24
12-05-24	18-05-24	10​	27-05-24
15-05-24	17-05-24	30​	28-05-24
16-05-24	20-05-24	10​	29-05-24
19-05-24	25-05-24	15​	30-05-24

with taking in mind that the end date is not calculated as the rooms will be checked out on this date.

so i have been trying to see on each night how many room will i receive

i have been trying to manipulate using following SUMIFS


SUMIFS(C:C,A:A,"<=12-05-2024",B:B,">12-05-2024")

but i neet to make the date dynamic

 

[Rijnsent]

Hi Ahmed,
welcome to the forum.

=SUMIFS(C:C,A:A,"<=12-05-2024",B:B,">12-05-2024")

What you could do:

=SUMIFS(C:C,A:A,"<=" & K1,B:B,">" & K1)

You can put your date in cell K1, so it's dynamic. See this site for more examples: Sum if cell contains text in another cell