SUMIF and COUNTIF Functions in Excel – how can I use these functions in my worksheets?
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)
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)
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
This counts the number of cells which are non blank (<> is the symbol to use in Excel for not blank) and therefore exclude 2 cells in the range D2:D21 which have had no attendees.
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 on button to the right 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 the function NETWORKDAYS.intl in Excel?