I am using Microsoft Excel 2016 and I have a spreadsheet with multiple datasets that calculate a number of different things. For each site, we receive data for incremental reading throughout the whole year. This is for fifteen (15) sites, I am using one site for this description.
Once the incremental data is copied into the spreadsheet, a number of calculations exist. The following calculation summarizes the above-mentioned data to calculate the number of readings, column T in the image below, above a threshold, column U in the image below, that were taken over a single date.
=COUNTIFS(Regulator_7!$E$2:$E$100000,A2,Regulator_7!$C$2:$C$100000,U2)
Then the following calculation simplifies this, column V in the image below, to either a 1 or 0 using the following calculation.
=IF(T2>=1, "1", "0")
The 1 indicates an event happened on that date and the 0 indicates that no event happened on that date.
I now want to be able to summarize the number of events that happen in any given month. Here is the catch. If there is no event on the day before or the day after, it equates to a single event. If there are events on the before or the day after, the total number of events over multiple days equates to a single event. Therefore, January 1st, row 2, 5th, row 6, and 24th, row 25 would each be a single event. Calculating three (3) events in January. January 8th, row 9, 9th, row 10, and 10th, row 11 would need to be calculated as a single event rather the three (3) separate events. Therefore, there would be a total of four (4) events in January based upon the data I provided.