I am trying to create a attendee list
We are hosting a event for a week straight but some guests are coming the first day for 3 days, some are coming the 2nd day for less days, and so on, it’s a big group so i am trying to create a list of each day how many attendees we have that day…
So we will get a list of everyone attending, with
“from what date, to until what date they will be attending.
So I am trying to create a formula that will easily calculate from that list how many attendees we have each individual date…
With out this formula it will be very hard to calculate each day how many attendees we have, especially when it comes to a few hundred attendees
—–
Avi
Lots of ways to do it. If B2 contains the start date, and C2 contains the end date, then D2 and on contains the 7 days of the week. Let's say D1 contained the actual date value. So in D2, you'd have a formula that looked at D1 and B2, and also D1 and C2. So then:
IF(AND(D$1≥$B2,D$1≤$C2),"✅","")
Pay careful attention to the absolute row/column indicators. The $. D$1 means that only the column changes as you copy and paste from D2 to E2, the row, 1, stays the same. $B2 means that B remains steady, but the row changes as you paste into D3, D4, D5, etc.
Then it is just a matter of using the footer row with COUNTIF. So the footer row in D would be COUNTIF(D,"✅").