Tuesday, 21 September 2010

Conditional text in Excel when a range has a value

Unusually busy on the Excel helpdesk today – Jane's follow-up question was how to do the same thing with a range of cells. eg.

image

My initial suggestion was to use COUNTIF(range, criteria) where the criteria is "*", but it turns out there's a better answer – COUNTA(range) which counts the number of cells in the range with are non-empty. So the formula becomes:

=IF(COUNTA(B3:C3), "Y", "N")

If you wanted to do the inverse and only return "Y" if all values in the range are blank, then COUNTBLANK(range) will do the job. Also check out KB 274537 for details on some scenarios when COUNTBLANK doesn't work so well.

1 comment:

Anonymous said...

excel-lent advice, thanks Dave!