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.

count functions in excel

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

example excel

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.

count shot

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.

countif

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.

countifs

final shot

countifs and countifs

Advertisements

About Steve Chase

I want to help you work in your business, so you can work on your business. I want to help you achieve your goals and help you have a successful small business journey. I understand that keeping up with the latest technology and cloud based computer trends can be difficult, when you’re running your day to day business. But, it is crucial that you keep up with the technology and best understand your options, in order to thrive in this new era of online business. I can help you discover the right software and tools for your business, train your team on how to use them, and mentor you to best serve and delight your customers. My certifications include: QuickBooks ProAdvisor, Microsoft Office Specialist Master, and OntraPort Certified Consultant. Please contact me on my new website at www.sequentiasolutions.com
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?

    Pls advice

    Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s