Fresh Excel Tips

In this example, Column A is demographic information (e.g., BLONDE, BRUNETTE, RED, GRAY, BLACK) and Column B is a number. We are working to get the average of the numbers in Column B for which A=Blonde while at the same time, ignoring empty cells. Although there are many ways to achieve this, in this example we will use the SUMPRODUCT Function. Here is how we’ll achieve this:

How to get the average of cells while ignoring empty cells

1. Enter your text description in Column A from A1:A10 and numbers in Column B from B1:B10.
2. In CELL C1, Enter the following formula: =SUMPRODUCT(–(A1:A10=”BLONDE”),B1:B10)/SUMPRODUCT(–(A1:A10=”BLONDE”),–(B1:B10<>”"))
3. Press Enter

This formula will return the averages of all BLONDE while ignoring the empty cells, if there are any, so it won’t affect the average calculation. This tip will work in Excel 97, 2000, 2003 and 2007.

1. How to Use the COUNTBLANK function to count empty cells
2. How to calculate the average output using the AVERAGE function
3. How to Use the PRODUCT function to multiply conditional values
4. How to Use IF/then/Else Conditional Statements in Excel VBA
5. How to use the SUMPRODUCT function in a formula in Excel