Leading the way in Microsoft Office Development

The named range "CellRange" refers to the block of cells "B2:G9"

How many cells are greater than 5

{=B2:G2>5} = {TRUE , TRUE , FALSE , FALSE , TRUE , TRUE}

How many cells contain error values

{=SUM(IF(ISERROR(CellRange),1,0))} = 2

How many cells contain numerical values

{=SUM(IF(ISNUMBER(CellRange),1,0))} = 46

How many cells contain unique numeric values

{=SUM(IF(FREQUENCY(CellRange,CellRange)>0,1,0))} = #N/A

This formula does not work when some of the cells contain error values.

Adding up a cell range that contains errors

{=SUM(IF(ISERROR(CellRange),"",CellRange))} = 623

Adding up a cells that meet a condition

{=SUM(IF(ISERROR(CellRange),"",IF(CellRange>15,CellRange)))} = 402

Adding up a the Nth largest numbers

{=SUM(LARGE(CellRange, ROW(INDIRECT(Row(CellRange) & ":" & Rows(CellRange)))))}

Adding up a the Nth smallest numbers

{=SUM(SMALL(CellRange, ROW(INDIRECT(Row(CellRange) & ":" & Rows(CellRange)))))}

Averaging all the numbers excluding zero

{=AVERAGE(IF(CellRange<>0,CellRange))}

Finding a matching value

{=IF(OR(ValueToFind = CellRange),"Match","No Match")}

Counting the number of differences between two cell ranges

{=SUM(1*(CellRange <> CellRange_Compare))}

Locating the Minimum value

{=ADDRESS(MIN(IF(CellRange=MIN(CellRange),ROW(CellRange), "")), COLUMN(CellRange))}

Adding up the individual digits of a number

{=SUM(VALUE(MID(ABS(Cell_Address),ROW(INDIRECT("1:"&LEN(ABS(Cell_Address)))),1)))}

Finding the closest match

{=INDEX(CellRange,MATCH(SMALL(ABS(CellAddress - CellRange),1),ABS(CellAddress - CellRange),0))}

Adding up a cells that meet two conditions

{=SUM((CellRange>5) * (CellRange<15) * CellRange)}

Returns #N/A if any of the cells are not numeric

{=SUM((IF(ISERROR(CellRange),0,CellRange)>5)*(IF(ISERROR(CellRange),0,CellRange)<15)*IF(ISERROR(CellRange),0,CellRange))} = 156

Adding up a cells that meet either condition

{=SUM(IF((CellRange<5) + (CellRange>15), CellRange))}

Returns #N/A if any of the cells are not numeric