# Count number of active days for a specific ID

1. ## Count number of active days for a specific ID

Hi,

I need some help I'm trying to identify the total number of active days for a particular "Event ID" (column H) against the "Date" column (column A) and populate this in the "Number of nights active?" column (Column G).

As a guide to what I am looking for, I have manually populated column G (image below and attachment included) with what results I want inputted but with the use of the correct formula, which I hope you guys can help me with please

For example, with Event ID 211966, the number of nights active is "2" this is because this event ID is active between the dates 03/10/2018 - 05/10/2018 for 2 nights (3rd and 4th).

I also expect certain rows the number of nights active column to remain as 0 if all the dates for a particular Event ID is the same. For example, with Event ID 211819, all the events that occurred are on the same date (01/10/2018), so it should not count anything and return a 0 value, as I only want the formula to do a count for when the dates for given Event ID is a different date (each night = +1 count)

The same also occurs with Event ID 211825, where I have 3 different dates, 01/10/2018, 02/10/2018 and 03/10/2018, so the expected result is 2, as its 2 nights between the 3 dates.

I would really appreciate it if someone could help me with this please? What formula would be best to use? Would it be a MIN/MAX formula or something else totally different?

I am willing to clarify anything if what I have said does not make sense!

Capture4.PNG

2. ## Re: Count number of active days for a specific ID

Try this in G2:

=LOOKUP(2,1/([Event ID]=H2),[Date])-INDEX([Date],MATCH(H2,[Event ID],0))

3. ## Re: Count number of active days for a specific ID

Originally Posted by 63falcondude
Try this in G2:

=LOOKUP(2,1/([Event ID]=H2),[Date])-INDEX([Date],MATCH(H2,[Event ID],0))
Thank you! It works!

However, if possible, are you kindly able to explain what your formula is doing please? I would appreciate your help

4. ## Re: Count number of active days for a specific ID

Sure.

=LOOKUP(2,1/([Event ID]=H2),[Date])
returns the last date where [Event ID]=H2

=INDEX([Date],MATCH(H2,[Event ID],0))
returns the first date where [Event ID]=H2

Subtracting the first date from the last date, gives us the number of days.

5. ## Re: Count number of active days for a specific ID

Originally Posted by 63falcondude
Sure.

=LOOKUP(2,1/([Event ID]=H2),[Date])
returns the last date where [Event ID]=H2

=INDEX([Date],MATCH(H2,[Event ID],0))
returns the first date where [Event ID]=H2

Subtracting the first date from the last date, gives us the number of days.
Thank you very much for that!

6. ## Re: Count number of active days for a specific ID

Happy to help.

There are currently 1 users browsing this thread. (0 members and 1 guests)