Statistical
Conditional Median
Calculate the median of values that meet specific conditions. Unlike AVERAGEIFS, Excel doesn't have a built-in MEDIANIFS, so we use an array formula approach.
Excel Formula
=MEDIAN(IF(A2:A100=D2, B2:B100))Step-by-Step Explanation
1
This is an array formula (Ctrl+Shift+Enter in older Excel)
2
IF(A2:A100=D2, B2:B100) filters values where A matches D2
3
Non-matching values become FALSE and are ignored
4
MEDIAN then calculates the median of filtered values
5
In Excel 365 / Google Sheets, it auto-spills without Ctrl+Shift+Enter
Example
| Region (A) | Sales (B) | Median by Region |
|---|---|---|
| West | $15,000 | West: $18,000 |
| East | $12,000 | East: $14,500 |
| West | $21,000 | |
| East | $17,000 | |
| West | $18,000 |
Result: =MEDIAN(IF(A2:A6="West",B2:B6)) → $18,000
Common Variations
Multiple conditions
=MEDIAN(IF((A2:A100=D2)*(B2:B100=E2),C2:C100))AND logic with multiply
Conditional mode
=MODE(IF(A2:A100=D2,B2:B100))Most common value with condition
Conditional count
=COUNTIF(A2:A100,D2)Count matching values
Need a Custom Version?
Use our AI generator to create a formula tailored to your specific data and requirements.
Try It with AI →