SUMIFS formula that takes first seven of matching criteria from an array

Hi,

I'm trying to use a SUMIFS function with two sets of criteria;
1. the ID of an operator,
2. the 7 most recent weeks that this operator was active

Based on the example below, I essentially want to create a list in which I sum up used Resources (Column C), by a particular Operator (Column A), in the most recent 7 actions based on the week of occurence (Column B).

This has to be a dynamic formula (everything is variable) as I'm dealing with over 200 ID's and 4 more phases of which I need to sum these resources up.

I've got something like =SUMIFS(C:C,A:A,73,B:B,"<"&$A$1)
I presume I have to insert an OFFSET(...MATCH(...ROW))) somewhere in the last criteria part of this but I haven't been able to wrap my head around this (4 days non-stop spreadsheet analysis may be the cause I'm overlooking the simple solution here...)

image.png

Hi Glenn,

That's correct. However, as I compile more data, this array will move to count from 201517 once this data is added (based on value of cell A1, not by going from empty to a value).

Hi Glenn,

Thanks for looking into this. The problem is that the way the list is organized is variable. I continuously need to sort and filter this list which affects the results of the formula if Column B isn't sorted small to large. It should be workable though by simply doing this before extracting the data to analyze.

The #REFs shouldn't occur; I trimmed the data for the example and may have overlooked a reference to a deleted cell or external file. However the text in column C is essential to work-processes prior to data collection.
I tried to insert various forms of this into the formula: IFERROR(VALUE(C$3:C$340),"") to omit any cells containing text, but unsuccessfully so. (I entered as an array formula FYI)

Ideally the formulas could deal with this, but for now I'll copy the data to a new sheet, insert a column with the above formula and use that column for "resources".

This is very helpful to extract the data I need this week. Thanks!

---

I'll take this a step further; By comparing Produced/Resources I derive a productivity number (in the case of past seven weeks ID 73, 8864/2420=3.66). This is a 7-period, weighed average I use for planning production outcome scenarios. This is easily done using your formula.

The next step is finding the maximum positive and negative deviations from that average. This is essentially the only thing I'm looking for but the big challenge is that I can't add columns to my list (various non-excel related reasons).
Now, I'm projecting the 7 most recent productivity numbers in the report sheet and take the MIN and MAX from the 7 period total. I believe there could be a more elegant solution than using 10 columns for each production phase, but it's a good start.

I've expanded on your formula and attached the example if you would like to elaborate more on this.

Cheers!

Thanks Glenn,

Good advice on the 'right' function alternative.

I've made the positive and negative deviation formulas a bit lighter by simply subtracting the 7-period average from either the MAX or MIN in that series and then divided by the same average to get the percentage as can be seen below.

Below an extract of the larger data I'm working with at the moment.

image.png

Cheers!

I realize this might be difficult to trace from a screenshot:

=(MIN(OFFSET(INDIRECT(CELL("address",INDEX('Realised amounts'!$R:$R,MAX(($A98='Realised amounts'!$D:$D)*('Realised amounts'!$R:$R>0)*MATCH(ROW('Realised amounts'!$R:$R),ROW('Realised amounts'!$R:$R)))))),0,0,-IF(AB98<7,AB98,7),1))-B98)/B98

This looks up the data from a new column in the list. It was impossible to work around that considering text or error values anyway, so I added a productivity number to each line.