SUMIF and COUNTIF Functions in Excel – what do they do?

SUMIF and COUNTIF Functions in Excel: Excel iconSUMIF and COUNTIF Functions in Excel – using these functions in your spreadsheet, have you used them before? Watch the video to learn more

​This week’s hint and tip is about the SUMIF and COUNTIF functions in Excel and how you can use them in your worksheets. These are covered on our intermediate Excel course but we also decided to do a hint and tip on it. We are going to go through it now below.

SUMIF and COUNTIF Functions in Excel

This blog goes alongside the video we’ve made on our YouTube channel that you can watch below in the post.

Whereas two really common functions in Excel are =SUM and =COUNT, these are simple and simply take a range as the argument inside the brackets without any criteria applied to the cells in that range. SUMIF and COUNTIF go a step further.

We have 3 examples (2 of SUMIF and 1 of COUNTIF) to illustrate this. The worksheet is the same for all 3 and is attached also to this blog below for you to see.

In this worksheet we have a list of customer courses run on separate dates with their corresponding month number besides it and the number of attendees in column D.

Example 1 – SUMIF (using 2 arguments)

=SUMIF(D2:D21,”>5″)

This adds up the number of attendees where there are more than 5 people on a course in the range D2:D21.

Example 2 – SUMIF (using 3 arguments)

=SUMIF(C2:C21,”<=3″,D2:D21)

This adds up the numbers in the 3rd argument i.e. D2:D21 but bases that addition on the range of cells in column C if the month is less than or equal to 3.

Note there has to be the same range of rows in arguments 1 and 3 for this to work.

Example 3 – COUNTIF

=COUNTIF(D2:D21,”<>”)

This counts the number of cells which are non blank and therefore exclude 2 cells in the range D2:D21 which have had no attendees.

These 3 functions and more are taught on our virtual and onsite intermediate Excel course. For more information on our virtual course, please visit here and for our onsite course here.

For more information on any of our courses please visit our website or contact John Legge on johnlegge@jplcomputer.co.uk.

The video below shows you how to actually use these functions in a spreadsheet. We hope that you find the video useful and enjoy learning about it!

Take a look below at the video to find out more and then try it out on your own computer!

Click here to download the example spreadsheet shown in the video to try it out yourself!

We hope you have enjoyed this hint and tip on SUMIF and COUNTIF functions in Excel. Why not take a look at our previous one on protecting documents in Word​?

Please visit our YouTube Channel directed from our website or via this link for more videos.

Cookie Control

Cookie control

We have placed cookies on your device to help make this website better.

I'm fine with this

We use cookies to give you the best online experience.

Please let us know if you agree to all of these cookies.

Some of the cookies we use are essential for the site to work.

We also use some non-essential cookies to collect information for making reports and to help us improve the site. The cookies collect information in an anonymous form.

To control third party cookies, you can also adjust your browser settings.

I'm fine with this
(One cookie will be set to store your preference)
(Ticking this sets a cookie to hide this popup if you then hit close. This will not store any personal information)
Information and Settings Cookie policy