Seeking excel formula based on multiple criteria using a key

Question

I have asked three developers and one VBA guru on my team who have no idea how to proceed with the following inquiry:

I have a KEY that has outlined several groups (the picture is only showing two). These groups have different AUX definitions. Productive AUX hours are indicated by a "W".

In my Clerk Data tab, I have imported daily data based on the various groups. The snapshot shows an example of group 1 vs group 2 data.

I am looking for a formula to put into column O that will match the Group in column N to Column A in the key. If it matches, then I want it to add up the aux fields based on if there is "W" populated.

If I were to manually calculate this, O30 would equal 15.4 hours.

In O31, it would be 5.75 hours.

Is a single formula possible in this scenario?

Answer 1

This sums the values based on your key. This assumes that you keys are stored in a sheet named Key.

=SUMIF(OFFSET(Key!$B$1:$K$1,MATCH(N30,Key!A:A,0)-1,0),"W",C30:L30)

SUMIF takes (range, criteria, sum_range) where the range and sum_range have to be the same size. It will sum the values in sum_range, C30:L30, if the equivalent cell in range matches the criteria, that is equals W.

range is OFFSET(Key!$B$1:$K$1,MATCH(N30,Key!A:A,0)-1,0).

OFFSET takes (reference, rows, cols) where the reference is Key!$B$1:$K$1, which will be offset by the number of rows from MATCH(N30,Key!A:A,0)-1.

MATCH takes (lookup_value, lookup_array, match_type) which will find the row that matches your Group #, N30, in the range, Key!A:A. match_type set to zero means it will look for an exact match.

Answer 2

=SUMIF(Sheet2!B2:K2,"W",Sheet1!C31:L31)