{"id":313,"date":"2015-06-24T10:46:49","date_gmt":"2015-06-24T10:46:49","guid":{"rendered":"http:\/\/www.ahbsolutions.co.uk\/blog\/?p=313"},"modified":"2017-03-29T14:43:06","modified_gmt":"2017-03-29T14:43:06","slug":"using-count-functions-in-excel","status":"publish","type":"post","link":"https:\/\/www.ahbtraining.co.uk\/blog\/2015\/06\/using-count-functions-in-excel\/","title":{"rendered":"Using Count Functions In Excel"},"content":{"rendered":"

I was called upon this week to solve a problem using the Excel Count functions. The situation was that the user just didn’t understand the difference between each of the functions available, which I will summarise here.<\/p>\n

There are 5 Count functions<\/p>\n\n\n\n\n\n\n\n
COUNT<\/td>\nCounts how many numbers are in the list of arguments<\/td>\n<\/tr>\n
COUNTA<\/td>\nCounts how many values are in the list of arguments<\/td>\n<\/tr>\n
COUNTBLANK<\/td>\nCounts the number of blank cells within a range<\/td>\n<\/tr>\n
COUNTIF<\/td>\nCounts the number of cells within a range that meet the given criteria<\/td>\n<\/tr>\n
COUNTIFS<\/td>\nCounts the number of cells within a range that meet multiple criteria<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

For this example consider the following spreadsheet<\/p>\n

\u00a0<\/a>\"2015-08-27_11-20-34\"<\/p>\n

Using Count to count all the numbers produces the following.<\/p>\n

\"2015-08-27_11-21-14\"<\/a><\/p>\n

The value for the count is 23 as the count renege is A2:C13, so this includes the 12 entries for dates and the 11 entries for the monetary values.<\/p>\n

However if using COUNTA to count all the entries<\/p>\n

\"2015-08-27_11-22-25\"<\/a><\/p>\n

This counts all the cells in the range used (A2:C13), that have values in the cells. This function counts numbers and text values, but not the blank cell (C8).<\/p>\n

And COUNTBLANK to count the number of cells without any entries<\/p>\n

\"2015-08-27_11-22-59\"<\/a><\/p>\n

There being only one blank cell in the range A2:C13 this is the only cell that is counted.<\/p>\n

Now using COUNTIF to count the cells in column C of the table that have values greater than \u00a3500 produces a total of 7. This uses a single criteria to produce the result.<\/p>\n

\"2015-08-27_11-29-11\"<\/a><\/p>\n

Finally expanding on the above, by using COUNTIFS allows additional criteria to be added, in this case the dates were included in the criteria to extract only those entries in the table that have dates in April, and greater than \u00a3500.<\/p>\n

\"2015-08-27_11-30-03\"<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

I was called upon this week to solve a problem using the Excel Count functions. The situation was that the user just didn’t understand the difference between each of the functions available, which I will summarise here. There are 5 Count functions COUNT Counts how many numbers are in the list of arguments COUNTA Counts … <\/p>\n

Continue reading<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[29,10,14,22,23],"tags":[],"class_list":["post-313","post","type-post","status-publish","format-standard","hentry","category-excel-2007","category-microsoft","category-office-2007","category-tips-and-tricks","category-training","item-wrap"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.ahbtraining.co.uk\/blog\/wp-json\/wp\/v2\/posts\/313","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ahbtraining.co.uk\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ahbtraining.co.uk\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ahbtraining.co.uk\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ahbtraining.co.uk\/blog\/wp-json\/wp\/v2\/comments?post=313"}],"version-history":[{"count":1,"href":"https:\/\/www.ahbtraining.co.uk\/blog\/wp-json\/wp\/v2\/posts\/313\/revisions"}],"predecessor-version":[{"id":444,"href":"https:\/\/www.ahbtraining.co.uk\/blog\/wp-json\/wp\/v2\/posts\/313\/revisions\/444"}],"wp:attachment":[{"href":"https:\/\/www.ahbtraining.co.uk\/blog\/wp-json\/wp\/v2\/media?parent=313"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ahbtraining.co.uk\/blog\/wp-json\/wp\/v2\/categories?post=313"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ahbtraining.co.uk\/blog\/wp-json\/wp\/v2\/tags?post=313"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}