How to Count in Excel using COUNT, COUNTA, COUNTBLANK, COUNTIF, and COUNTIFS

This post will explain all the different count functions in Excel. See graphic below that has a brief description of what each formula does.

For this demo I will use the following data from the screen shot below.

COUNT(A2:C5) would look at the range in blue and return the value 7 since it counts cells that contain numbers. COUNTA(A2:C5) would return 11 because it counts anything with text or numbers. COUNTBLANK(A2:C5) would return 1 because there is a blank cell in the range.

If I wanted to count how many times the word “excellent” is listed I would use the COUNTIF function. This has 2 arguments. The first is the range and the second is the criteria. We would get the value 2 answer.

The last example in this post will look at multiple conditions for the criteria using the COUNTIFS. You use COUNTIFS when you have 2 or more conditions. My example will share counting how many rows of data do we have that have an “excellent” status and the units is greater than or equal to 90. You put “>=90” inside of quotes.There will be 4 arguments. Make sure that each range is the same amount of cells.  The answer value returned is 1.

This entry was posted in Excel. Bookmark the permalink.

2 Responses to How to Count in Excel using COUNT, COUNTA, COUNTBLANK, COUNTIF, and COUNTIFS

1. Akram G says:

I diidnt know where to post my issue, so am posting here, sorry for any incon

Hello,
I have an Excel file in which there are 13 sheets (one for each month and as Summary (FY sheet) of the expenses listed on each month sheet).

The individual sheets are named Jan 17, Feb 17, Mar 17 and so on.

In the month individual sheet (say Mar 17) there are 4 Columns;
Date (Column A), Paid By (Column B), Category (column C) and Amount (Column D)

In each month sheet, there is a grand total for the amount spent in the entire month and to calculate the sum for each Category (Column C) this formula
=SUMIFS(\$D\$4:\$D\$14, \$C\$4:\$C\$14,”Brokerage”)
=SUMIFS(\$D\$4:\$D\$14, \$C\$4:\$C\$14, “F&B”)
=SUMIFS(\$F\$4:\$F\$20, \$D\$4:\$D\$20, “Office Supplies”)
and so on

My propblem is in the Summary (FY sheet) at the top the months are listed in ROW A

In Column A, the different Category of expenses are listed. Now I need to reflect against each of these categories their monthly expenses incurred (Under Jan the expenses from Jan 17 sheet, Under Feb the expenses from Feb 17 sheet, and so on)

How do I go about it??

Can I use the SUMIF? If yes what is the format?