Complicated array function with multiple criteria

Question

I have tables which look like this:

Link

I want to filter out the sum of the maximum values in column C for every unique text value in column A, where the value in column B equals "d".

So for the linked example, the result would be a sum of C3+C6+C9 equaling 9. I am able to get the right results using pivot tables, macros etc., but I'm looking to solve it with a single function.

Scott Craner · Accepted Answer · 2018-06-28 14:29:10Z

3

Use:

=SUMPRODUCT(MAXIFS(C:C,A:A,A1:A9,B:B,"d")/(COUNTIFS(A:A,A1:A9)))

MAXIFS was introduced with Office 365 Excel

answered Jun 28, 2018 at 14:29

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dag Over a year ago

Thanks! Any clues on how to do this in older versions of ecxel?

Scott Craner Over a year ago

@Dag I could not find one that did not include a helper column

Collectives™ on Stack Overflow

Complicated array function with multiple criteria

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related