1

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.

Incremental Readings

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.

Summary Per Day

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.

1 Answer 1

2

This counts where the dates fall in the month of January and the Regulator 7 Level is greater than or equal to 1 AND the next value is 0.

=COUNTIFS(A2:A1000,">=1/1/2019",A2:A1000,"<=1/31/2019",T2:T1000,">=1",T3:T1001,0)

Note the same size but offset ranges.

enter image description here

5
  • Awesome, Is there a way to truncate the date to remove the year? I was hoping to make this spreadsheet universal with copying the data into another worksheet for each site, then everything could just populate
    – LandArch
    Jan 9, 2020 at 20:27
  • Not with true dates, if you add a column that has the month then you can remove the date parts and just look for the correct month. Jan 9, 2020 at 20:41
  • Thanks for the tip. Here's another thought. Is there an easy way to update the calculations to change the year? With 16 sites and 12 months, this is 192 calculations.
    – LandArch
    Jan 9, 2020 at 21:02
  • The date is just a string so you can concatenate the year desired from another cell. Jan 9, 2020 at 21:35
  • I'm not sure I understand how that would work, and changes all the formulas. Should this be another post?
    – LandArch
    Jan 10, 2020 at 13:41

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .