CODEWITHSUNDEEP
excelexcel-formula
Dag
Dag

Reputation: 13

Complicated array function with multiple criteria

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.

Upvotes: 1

Views: 56

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

Use:

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

MAXIFS was introduced with Office 365 Excel

enter image description here

Upvotes: 3

Related Questions