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

Try this in G2:

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

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.

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!

Happy to help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.