Identifying Max on Two Conditions

  • Thread starter Thread starter RFJ
  • Start date Start date
R

RFJ

I wish to find the maximum value in Column C providing conditions in Columns
A and
B are met.

eg in the following example

Fred A 120
John B 200
Fred B 400
Fred A 50

The english would be =MAX((Column A ="Fred")AND(Column B="A")),Column C)

The answer would be 120.

I also would like to be able to do it for Median and Average

TIA
 
Hi
try the following array formulas (entered with
CTRL+SHIFT+ENTER):
1. Max:
=MAX(IF((A1:A100="Fred")*(B1:B100="A"),C1:C100))

2. Avergae:
=AVERAGE(IF((A1:A100="Fred")*(B1:B100="A"),C1:C100))

3. Median:
=MEDIAN(IF((A1:A100="Fred")*(B1:B100="A"),C1:C100))
 
Back
Top