Calculating the sum of a range or an area range where the start point can move

[Jules_Excel]

Hi folks,

I've been tasked to create a spreadsheet which looks at the current month and sums the number of incidents in either a single range, or an area range, depending on a set of applied rules, based on a start point of the current month and looking back either 6 months, or 12 months, depending on which rule is being applied.

The data area will expand back 24 months, so as to cover the whole of the current year and of the previous year, but only a sufficient count within the scope of the rule-defined ranges should trigger the logic state to change. My example method (provided) broke on a 24 month history, as the logic kept triggering out of scope of the rules, so I really could use a better way of doing this.

I've bodged together a semi-working example covering just 1 year.

The rules are as follows:

#1: Count 2 or more under any singular category in any 6 months = Discuss
#2: Count 3 or more under any combined categories in any 6 months = Discuss
#3: Count 4 or more under any combined categories in any 12 months = Alert
#4: Any additional count over 4 in any category in any 12 months = Strong Alert


What I want to do to plot the area range (Please note: This is not how I've done it in the example sheet- this is the part I need help with):
- The start column position is given by a match based on the current month.
Note: This month value is currently set to a static value, but no need to worry about that.

- The starting row position is offset down by 2 to put the start point in the data area
- The end row range expands down 8 to the bottom of the data area
- The end column range looks left by 6, or 12, depending on the rule being applied
- The trigger logic is determined by each rule, depending on the sum in the defined area/range

Feel free to scrap my method entirely! - I'm really looking for a better, slicker example method of how to achieve this.

Thanks in advance!

 

[Jules_Excel]

Indeed so in retrospect. I have acknowledged your point regarding disambiguation.

Should I post in the future, I'll keep a closer eye on the precision of the terminology; for my part, I did my best to convey what I believed to be a clear description of requirements. Nonetheless, my apologies if this lead to undue elaboration on your part when creating your solution.

Once again, your help is appreciated.

 

[KRice]

No problems. The important thing is that you have a solution that works. My misinterpretation of the objectives led to an offering that was both incorrect and unnecessarily complex. Thanks for the follow-up.