CODEWITHSUNDEEP
excelexcel-formula
Rahul
Rahul

Reputation: 51

Select all rows within a range using IF condition in EXCEL

I am trying to calculate Median of a Column A based on 2 criteria from other columns B and C. Condition for criteria in B and C are in cells H1 and E6 respectively, which contain a list of values.

I am using the following formula to calculate median based on criterias

= MEDIAN(IF((TableName[[Column B]:[Column B]]= $H$1)*(TableName[[Column C ]:[Column C]]= $E$6), 
  TableName [[Column A]:[Column A]]))

I would like to not consider the condition in Column C if the input in E6 is "ALL". In other words, I am looking for a solution to consider all values in Column C IF the input in E6 is "ALL"

Any help would be much appreciated.

Thanks RV

Upvotes: 0

Views: 318

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

Add another IF:

= MEDIAN(IF((TableName[[Column B]:[Column B]]= $H$1)*(IF($E$6 = "ALL",TRUE,TableName[[Column C ]:[Column C]]= $E$6)), TableName [[Column A]:[Column A]]))

Upvotes: 1

Related Questions