Formula Friday - COUNTA (Count non-blank cells)

An easy way to count cells that have data in Excel is by using the 'COUNTA' formula.  This formula will count cells that are not blank, whether they are numeric or anything else.  This is different from the COUNT formula that only counts cells if they have a number in them.

To use this formula:

-Click on the cell where you want to see the count results

-Enter COUNTA(Number1:Number2)

-The range of cells to be evaluated can be either consecutive or non-consecutive

-Like in other formulas, to evaluate consecutive cells, the cell range will use a ':' between cells

-i.e. COUNTA(B1:B15)

-To evaluate non-consecutive cells, the cells will be separated by a ','

-i.e. COUNTA(B1,B5,B8)

-Or, a combination can also be used

-i.e. COUNTA(B1:B5,B9)

-Press Enter

-Now you will see the number of non-blank cells in the result cell

Example:

-In this scenario, I want to count the number of months where the fruit or vegetable was tracked (has an "X" in the cell).

-I want the results to be displayed in row 17

-For this example, I will just focus on counting the Bananas to start with

-In cell B17, enter =COUNTA(B5:B16)

-Press Enter

-You will now see the result of 4 as there are 4 months that have an 'X" in the cell (January, June, August & November)

-Now I can copy this formula across to cells C17 through M17 to get the results for the other Fruits/Vegetables

-Results

 

Excel ya later!