COUNTIF Issues

[M12NIX]

I have butlers assigned to guests' rooms. I need to calculate the number of rooms they have arriving, departing and those that are still 'in house'

Each guest has a membership level. (yellow, green & red as per my example)

I have worked out the formulas for the arrivals and departures but I cannot work out the 'in house' ones. Please help.

In the attached file, cells A11:G20 are fully editable. This file will be worked upon daily with people with very little excel knowledge.

Cells A1:A6 have to stay the same. Cells C3:K6 are the results I am looking for. The red columns are what I cannot work out.

Cells J12:R15 are the desired results

[Sam Capricci]

if you are saying the results you expect are in cells J12 through R15, I'm having trouble seeing how those numbers match what I think you are saying the formula should be returning. For example Matthew has 0 in-house on 4/23/23 but it looks to me that Matthew has 2 in-house, row 16 where Jones arrives 4/22 and departs 4/25 and is yellow level, also Smith, row 19 arrives 4/22 and departs 4/29 and is yellow.
I came up with these three sumproduct which look to me like they work,
for yellow, green and red in-house =SUMPRODUCT(($G$11:$G$20=$I12)*($C$11:$C$20=J$10)*($D$11:$D$20<=$A$1)*($E$11:$E$20>=$A$1))

for yellow, green and red arrivals =SUMPRODUCT(($G$11:$G$20=$I12)*($C$11:$C$20=K$10)*($D$11:$D$20=$A$1))

for yellow, green and red exits =SUMPRODUCT(($G$11:$G$20=$I12)*($C$11:$C$20=L$10)*($E$11:$E$20=$A$1))

Another example, you have John as having one green arrival yet the only row I see for John is row 20 and the person arrived the day on the 22nd, not the 23rd so they appear to me to be in-house instead.
again, these work based on what I think your results would be but you know your data better than I.

[M12NIX]

Arrivals = column D equalling today (A1).
Exits = column E equally today.

In house is a calculation on everything that does not match A1. ie: those guests that have arrvied before today and are not leaving today.

Does that make more sense?

[Sam Capricci]

and, here is a way to adjust your countifs formula for in-house...
=COUNTIFS($G$11:$G$20,$A3,$C$11:$C$20,C$1,$D$11:$D$20,"<="&$A$1,$E$11:$E$20,">="&$A$1)

and I'd recommend getting rid of the references like "YELLOW" and green and red in the formulas and just refer to row 1 where those color codes are already (which is what I did with that formula).

[Sam Capricci]

Are you saying that the formulas I gave you are not giving the correct results?

[M12NIX]

They do not. But this is my fault.

I created the example on the 22nd and the data reflected that. I posted here on the 23rd so of course the TODAY formula changed everything

Attached is the correct information. (I replaced the original file I added.) There are 9 possible outcomes per butler as shown in the desired outcome table.

The formulas you send, thank you kindly, are counting the 'Arrival's & 'Exits' as 'In-House' thus coming back with 12.

Apologies for my oversight.

[Sam Capricci]

Yes, I was counting in-house as if they arrive on the 23rd and today is the 23rd that that would count as both an arrival and an in-house. So you are saying if they arrive on the 23rd and today is the 23rd AND they leave on the 25th that you only want to count the in-house as the 24th and nothing more?

if that is the case and you want to use the sumproduct then just remove the equal signs in the two parts looking at columns D and E like so...
=SUMPRODUCT(($G$11:$G$29=$I12)*($C$11:$C$29=J$10)*($D$11:$D$29<$A$1)*($E$11:$E$29>$A$1))

or make the countifs like this...
=COUNTIFS($G$11:$G$20,$A3,$C$11:$C$20,C$1,$D$11:$D$20,"<"&$A$1,$E$11:$E$20,">"&$A$1)

[M12NIX]

Thank you so much Sam. This is perfect. You're a lifesaver.

[Sam Capricci]

you're welcome, glad I could help. And if you are so inclined clicking on * Add Reputation under one of my posts is a way to say thank you as it is how we advance on this forum.