Seeking excel formula based on multiple criteria using a key

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?

data

Showing some example outputs usually helps.

– urdearboy
Aug 30 '18 at 16:27

I thought I had attached the picture in the link above?

– Jessica E.
Aug 30 '18 at 16:38

Is your output column not column O on the photo? If so, you are only showing inputs as the output column is blank. Sorry if I am misreading something! Plz clarify if I am :)

– urdearboy
Aug 30 '18 at 16:43

Yes, I am only showing inputs. I am trying to figure out how to sum the various AUX codes based on if the AUX Key (bottom half of the image) has a "W" in the field.

– Jessica E.
Aug 30 '18 at 16:49

So, in O30, if I were to manually calculate it, I would have 15.4;

– Jessica E.
Aug 30 '18 at 17:11

2 Answers
2

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

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.

SUMIF

(range, criteria, sum_range)

C30:L30

W

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

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.

OFFSET

(reference, rows, cols)

Key!$B$1:$K$1

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.

MATCH

(lookup_value, lookup_array, match_type)

N30

Key!A:A

Rey! Thank you! I think that's where I was caught up, I don't know much about OFFSET. I'll read more about it tonight but thank you for all your help! The formula worked great!

– Jessica E.
Aug 30 '18 at 20:48

Glad to help. If this works for you, please mark as answered to clear it out. Thanks.

– Rey Juna
Aug 30 '18 at 22:10

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

This will always grab the Group 1 information.

– Rey Juna
Aug 30 '18 at 19:35

Right, and careless on my part. Your answer is good.

– Ilan
Aug 30 '18 at 21:05

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

搜尋此網誌

Dfyjkt