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

SUMIF 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 training 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.