I'm trying to have a cell sum the maximum values in column C for each unique instance of A where B matches cell A1 on my sheet.

Pie Apple 3
Tart Apple 2
Pie Cherry 1
Pie Apple 5
Tart Cherry 4
Pie Cherry 4
Pie Cherry 6

So for this data above, when I have "Apple" in A1 on my sheet it would calculate 7 (based on 5+2) or when I have "Cherry" in A1 it would calculate 7 (based on 6+1).

I have tried QUERY but I'm thinking I need an arrayformula or vlookup combined with a query, but am struggling to identify the right method.

Can anyone point me down the right path?

  • Hi. Cherry would be 7 (6+1); sure you don't mean 10 (6+4)?
    – Tedinoz
    Commented Aug 18, 2022 at 8:08

1 Answer 1


There might be several ways to approach your problem. Try this formula:

=sum(query(A3:C9,"select A, max(C) where B contains '"&A1&"' group by A label max(C) ''"))

Returns the maximum value groups by Column A, and sums the result.

  • That worked great! Thank you so much for the quick help.
    – Jamie
    Commented Aug 18, 2022 at 16:40
  • If you thought this was a “good” answer, then you might consider voting for &/or “accepting” it.
    – Tedinoz
    Commented Aug 20, 2022 at 7:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.