Excel File

This is my first article on Microsoft Excel in over two years and continues the series with descriptions and examples of applying conditions to the popular Excel SUM, COUNT and AVERAGE functions.

I started writing this article about 6 months ago when one of my friends asked me how to count the number of occurrences in a spreadsheet where a condition in another column was true. I had thought that I had already written such an article and was about to point her to it but it wasn’t there. I remember it was on my list of articles to write about Excel but I’d obviously not written it yet. Anyway, I answered her with details of the COUNTIF function and started to get on with the article but then lots of things happened and I never finished it until today.

This article covers the basic functions COUNT, SUM and AVERAGE as well as the conditional versions of those; COUNTIF, COUNTIFS, SUMIF, SIMIFS, AVERAGEIF, AVERAGEIFS, AVERAGEA, COUNTA and COUNTBLANK. Before we get on to examples, I will provide a brief description of the function I will be covering.

Basic functions: COUNT, SUM and AVERAGE

The three basic functions here work on a range of cells. COUNT will count how many cells in a range have numeric values (dates can be included as numbers because Excel treats them as such). SUM will add up the values of all numeric values in a range of cells. AVERAGE will provide the mean average of all numeric values in a range of cells (i.e. The SUM divided by the COUNT). Each of these can contain one or move values or cell ranges.

The syntax of a single range is as follows:
=COUNT(range)
=SUM(range)
=AVERAGE(range)

The syntax of a multi-valued range is as follows:
=COUNT(range1, range2)
=SUM(range1, range2)
=AVERAGE(range1, range2)

You can replace any range such as A1:A6 with a number such as SUM(1,2,3,4).

Empty and non-empty cell functions: AVERAGEA, COUNTA and COUNTBLANK

AVERAGEA and COUNTA provide an average or a count of all cells that are not empty. For the purposes of averaging they treat non-numeric values as zero. There is no SUM version as a sum of values that contain text converted to zero will provide the same result as one that ignores non-text. However, AVERAGEA counts them when providing the mean. COUNTBLANK shows the number of empty cells. There are no AVERAGE or SUM versions of this as the value would return zero regardless.

The syntax of these is as follows:
=AVERAGEA(range)
=COUNTA(range)
=COUNTBLANK(range)

Two of these can have multiple ranges or values but COUNTBLANK only accepts a single range.

The syntax of the multiple range versions is as follows:
=AVERAGEA(range1, range2)
=COUNTA(range1, range2)

An example would be COUNTA(B2:B19,E2:F19, J2:J19).

Conditional functions: COUNTIF, COUNTIFS, SUMIF, SUMIFS, AVERAGEIF and AVERAGEIFS

Lastly, there are conditional versions of these functions. The IF versions require a single range and condition but AVERAGEIF and SUMIF include an option for a different value range. The IFS versions use a different syntax with a value range followed by one or more lookup ranges and conditions.

Here are all of the different versions of syntax:
COUNTIF(range, criteria)
COUNTIFS(range1, criteria1, range2, criteria2)
 

SUMIF(range, criteria)
SUMIF(range, criteria, sum-value-range)
SUMIFS(sum-value-range, range1, criteria1, range2, criteria2)
 

AVERAGEIF(range, criteria)
AVERAGEIF(range, criteria, average-value-range)
AVERAGEIFS(average-value-range, range1, criteria1, range2, criteria2)

In both AVERAGEIF and SUMIF the value range can be excluded and if they are then the function uses the “range” as the “value range”.

Example workbook

For examples of these functions I have used an Excel workbook that contains my purchases of Kindle and Audible books so far, this year. You can download the workbook here, count, sum, average conditional functions, if you want to follow through the examples.

The workbook contains two worksheets, one called “BookList” and one called “Questions”. The first contains a list of books that I have purchased together with Audible availability and purchase dates, and so on.

Excel Book List

The BookList worksheet has a cell range reference name for each column (which I’ve also shown in as row two). For instance, the “title” is the name of the range A3:A71.

The other worksheet has all the functions. All dates in the Workbook are shown in UK date format dd/MM/yyyy – some formula will need adjusting if your Short Date format is different.

COUNT, COUNTA, COUNTBLANK, COUNTIF and COUNTIFS examples

Excel Count

How many audiobooks are available to purchase?
=COUNT(aprice)
58 – This counts the number of numeric values in the “aprice” column. There are 58 numeric values in that column.

How many Kindle books purchased without Audible Add-on?
=COUNT(aprice)-COUNT(apurchased)
16 – This counts the number of numeric values in the “aprice” column and counts the number of numeric values in the “apurchased” column (dates are treated as numeric in Excel). There are 16 available Audible add-ons for the Kindle Books I have purchased. This can probably be found another way but I thought it was easier to take the number purchased away from the number available.

How many book titles have I purchased in 2017?
=COUNT(title)
0 – This counts the number of numeric values in the “title” column. There are no numeric values in that column, I could use a different numeric column such as pages.

How many book titles have I purchased in 2017?
=COUNTA(title)
69 – This counts the number of non-blank cells in the “title” column. This is a count of non-numeric values so that is OK.

How many audio books have I purchased in 2017?
=COUNTA(apurchased)
42 – This counts the number of non-blank cells in the “apurchased” column (dates are treated as numeric in Excel). This counts all values (numeric or not) but as all values are numeric in this column then I could have used COUNT.

How many books are not available as Audible an Add-On?
=COUNTBLANK(aprice)
11 – This counts the number of empty cells in the “aprice” column. Nearly all of the Kindle books I have purchased also have an Audible Add-on available to me although I haven’t taken up the offer on all of them.

How many still left to read or listen too from 2017 purchases?
=COUNTBLANK(read)
62 – This counts the number of empty cells in the “read” column which returns 62. I know I have purchased 69 so it means I’ve only manged to read or listen to 7 this year from those purchased in 2017. I could have used COUNTA to find the number of books that I have read.

How many books purchased by Simon Wood?
=COUNTIF(author,”Simon Wood”)
2 – This counts the books with “author” equalling a specific value. I enjoy Simon Wood’s books and have only purchased 2 this year as I’ve read most of them in previous years.

How many books purchased in 2017 have over 400 pages?
=COUNTIF(pages,”>400″)
10 – This counts the books with the number of “pages” greater than “400”. As you can see, you can use conditions such as greater than or less than as well as equals.

How many Books are listed with book titles beginning with ‘The’?
=COUNTIF(title,”The*”)
15 – This counts the books with “title” beginning “The”. As you can see, you can use wildcards such as “*” for any number of characters or “?” for any single character.

How many books have I read in 2017 that were the first in a series?
=COUNTIFS(read,”Y”,series,1)
3 – This uses two lookups, one for books that are “read” equalling “Y” and one for “series” numbered “1”. Like all Excel functions the number doesn’t need the quotes.

How many Kindle books cost between 2.00 and 4.00 each?
=COUNTIFS(kprice,”>=2.00″,kprice,”<=4.00″)
37 – The number of books in this “kprice” range between the specified values of “2.00” and “4.00”. You can have different lookup conditions on the same column.

How many books and audiobooks cost 3.49 or more for each?
=COUNTIFS(kprice,”>=3.49″,aprice,”>=3.49″)
34 – This one just counts those books that have both the value of Kindle books “kprice” and Audible books “aprice” over the value specified, “3.49”.

SUM, SUMIF and SUMIFS examples

Excel Sum

What is the cost of Kindle Books purchased this year?
=SUM(kprice)
213.75 – This adds up all the numeric values in a range.

What is the cost of All Books purchased this year?
=SUM(aprice,kprice)
411.67 – This adds up all the numeric values in a range and ignores any non-numeric or empty ones.

What is the cost of T.R. Ragan Kindle Books purchased this year?
=SUMIF(author,”T.R. Ragan”,kprice)
11.94 – This adds up all the numeric values in a range “kprice” for any row that contains “T.R. Ragan” in the “author” column.

What is the cost of T.R. Ragan Audible Books purchased this year?
=SUMIFS(aprice,author,”T.R. Ragan”,apurchased,”>0″)
10.97 – This adds up all the numeric values in a range “aprice” for any row that contains “T.R. Ragan” in the “author” column and has a numeric value (date) in the “apurchased” column.

How many book pages have I read/listened to from this list?
=SUMIF(read,”Y”,pages)
2,447 – This adds up all the numeric values in a range “pages” for any row that contains “Y” in the “read” column.

Cost of Audible Books available with Author names beginning with ‘M’?
=SUMIF(author,”M*”,aprice)
50.35 – This adds up all the numeric values in a range “aprice” for any row that contains text starting with “M” in the “author” column.

Cost of Audible Books purchased with Author names beginning with ‘M’?
=SUMIFS(aprice,apurchased,”>=0″,author,”M*”)
27.92 – This adds up all the numeric values in a range “aprice” for any row that contains text starting with “M” in the “author” column and has a numeric value (date) in the “apurchased” column.

What is the cost of Kindle Books purchased in May?
=SUMIFS(kprice,kpurchased,”>=1/5/2017″,kpurchased,”<=31/5/2017″)
79.69 – This adds up all the numeric values in a range “kprice” for any row that contains a date between May 1, 2017 and May 31, 2017.

What is the cost of Audible Books purchased in May?
=SUMIFS(aprice,apurchased,”>=1/5/2017″,apurchased,”<=31/5/2017″)
51.85 – This adds up all the numeric values in a range “aprice” for any row that contains a date between May 1, 2017 and May 31, 2017.

AVERAGE, AVERAGEA, AVERAGEIF and AVERAGEIFS examples

Excel Average

What is the average price of Kindle books in the list?
=AVERAGE(kprice)
3.10 – This adds up all the numeric values in a range of “kprice” and divides them by the number of numeric values in the range.

What is the average price of all books in the list?
=AVERAGE(kprice,aprice)
3.24 – This adds up all the numeric values in a ranges of “kprice” and “aprice” and divides them by the number of numeric values in the range.

What is the average number of books in a series?
=AVERAGE(series)
1.855072464 – This adds up all the numeric values in a range of “series” and divides them by the number of numeric values in the range. You should note that it includes 0 as a numeric value even though I was using 0 to mean the book was not part of a series.

What is the average rating I gave books this year?
=AVERAGE(rating)
4.000 – This adds up all the numeric values in a range of “rating” and divides them by the number of numeric values in the range. There is one cell that includes a non-numeric value “unfinished” but this function ignores that when working out the average.

What is the average rating I gave books this year?
=AVERAGEA(rating)
3.429 – This adds up all the numeric values in a range of “rating” and divides them by the number of non-empty cells in the range. There is one cell that includes a non-numeric value “unfinished” and this is treated as a book with a rating of zero. This affects the average.

What is the average date of Kindle Book purchases in 2017?
=TEXT(AVERAGEA(kpurchased),”dd/MM/yyyy”)
08/04/2017 – This function is just used to show that dates are treated as numbers and it converts the numeric result back to something resembling a date using the TEXT function. AVERAGE would do the same thing in this instance as there are no non-numeric values in that column.

What is the average date of Audible Book purchases in 2017?
=TEXT(AVERAGEA(apurchased),”dd/MM/yyyy”)
17/04/2017 – This function is just used to show that dates are treated as numbers and it converts the numeric result back to something resembling a date using the TEXT function. AVERAGE would do the same thing in this instance as there are no non-numeric values in that column.

What is the average price of Kindle books with ‘Crosswhite’ in the title?
=AVERAGEIF(title,”*Crosswhite*”,kprice)
3.98 – This averages all the numeric values in a range “kprice” for any row that contains text with “Crosswhite” in it from the “title” column.

What is the average number of pages per book read in 2017?
=AVERAGEIF(read,”Y”,pages)
349.57 – This averages all the numeric values in a range “pages” for any row that contains text with “Y” in it from the “read” column.

What is the average number of books in a series? Excludes 0 as a book!
=AVERAGEIF(series,”>0″)
2.56 – This averages all the numeric values in a range “series” for any row that contains a value greater than zero in the “series” column.

What is the average cost of Kindle Books purchased in April?
=AVERAGEIFS(kprice,kpurchased,”>=1/4/2017″,kpurchased,”<=30/4/2017″)
3.32 – This averages all the numeric values in a range “kprice” for any row that contains a date between April 1, 2017 and April 30, 2017 in the “kpurchased” column.

What is the average cost of Audible books that I have read this year?
=AVERAGEIFS(aprice,read,”Y”,apurchased,”>0″)
3.418571429 – This averages all the numeric values in a range “aprice” for any row that contains text with “Y” in it from the “read” column and a number greater than zero (a date) in the “apurchased” column.

Finally

The next time I get asked about conditional versions of these functions I can just point them to my article. If you are interested in other Excel functions then I have a few more examples in my Excel articles section and there will be more to add in the future.

I hope that you will find this article useful and your friends or colleagues do as well. Whilst I don’t have time to answer individual questions feel free to leave a comment about your experiences with these functions or if you have a better example of them then feel free to add it (no links though).

Leave a Reply

Be the First to Comment!

Notify of
avatar
wpDiscuz