{"id":4100,"date":"2018-11-20T14:04:38","date_gmt":"2018-11-20T19:04:38","guid":{"rendered":"http:\/\/lisa.rushworth.us\/?p=4100"},"modified":"2018-12-19T14:05:03","modified_gmt":"2018-12-19T19:05:03","slug":"did-you-know-you-can-use-microsoft-excel-to-count-the-number-of-records-within-a-range","status":"publish","type":"post","link":"https:\/\/www.rushworth.us\/lisa\/?p=4100","title":{"rendered":"Did you know \u2026 you can use Microsoft Excel to count the number of records within a range?"},"content":{"rendered":"\n

I\u2019ve been generating reports to track our Microsoft Teams adoption \u2013 how many people are using Teams, how many messages are being sent in Teams, how many Teams are there. Some of these metrics have easily visualized count-per-unit-time summaries available. Some, like the number of Teams, do not. <\/p>\n\n\n\n
\n Team<\/strong>\n <\/td>\n Created On<\/strong>\n <\/td><\/tr>
\n Directory Services\n <\/td>\n 1\/19\/2017\n <\/td><\/tr>
\n App Proxy\n <\/td>\n 1\/19\/2017\n <\/td><\/tr>
\n LDAP\n <\/td>\n 1\/19\/2017\n <\/td><\/tr>
\n ADFS\n <\/td>\n 1\/19\/2017\n <\/td><\/tr>
\n Nagios\n <\/td>\n 1\/19\/2017\n <\/td><\/tr>
\n File Cluster\n <\/td>\n 1\/19\/2017\n <\/td><\/tr>
\n Exchange Online\n <\/td>\n 1\/19\/2017\n <\/td><\/tr>
\n Active Directory\n <\/td>\n 1\/19\/2017\n <\/td><\/tr>
\n Commvault\n <\/td>\n 1\/19\/2017\n <\/td><\/tr><\/tbody><\/table>\n\n\n\n

But it\u2019s easy to turn a list of groups and creation dates into visualizable data. Paste the data into Excel. To find the number of items where \u201cCreated On\u201d falls in a range, we need to be able to define that range. 01 January 2017 is easy enough, but how do you get the end<\/em> of January? Excel has a function, EOMONTH<\/a>, that returns the last day of a month. <\/p>\n\n\n\n

Date is any date object. Offset is an integer number of months prior (negative numbers) or after (positive numbers) Date for which you want the last day of the month. I can\u00a0list the dates to start and end quarters with =EOMonth(Date,2).\u00a0With 01 January 2017 in cell D2, the\u00a0last<\/em>\u00a0day of January is =EOMonth(D2,0)<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

\u00a0I don\u2019t want to type01 Feb, Mar, April \u2026 flash fill <\/a>and the fill handle<\/a> need a few values before they can figure out the rest of a sequence. But I can use the last day of the month to get the first day of the next month \u2013 just add one! With 31 January 2017 in cell E2, I want =E2 + 1 in cell D3. (Yes, there are other ways to do this \u2013 probably dozens.)<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Now that we\u2019ve got a formula for the start and end of the month, just fill down<\/a> to produce the ranges we need to see how many Teams were created each month. Then we just need a formula to do the counting for us. I use the COUNTIFS<\/a> function. <\/p>\n\n\n\n

=COUNTIFS($B$2:$B$1000,”>=”&D2,$B$2:$B$1000,”<=”&E2)<\/p>\n\n\n\n

Counts the number of items in the range $B$2:$B$1000 (the cell range is static as the formula is copied elsewhere, hence the =D2\u201d would be comparing a number to a string, and you\u2019ll get 0. (opens in a new tab)”>$\u2019s indicating an absolute reference<\/a>) that are greater than or equal to D2 AND in the range $B$2:$B$1000 that are less than or equal to E2. We need to use an ampersand (&) to join the comparison operator and the cell \u2013 \u201c>=D2\u201d would be comparing a number to a string, and you\u2019ll get 0. <\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Fill down \u2013 you\u2019ll see the range remains static, and the comparison is to the D and E columns on the current row. <\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Voila \u2013 easily visualized data. And a graph \ud83d\ude0a\n<\/p>\n\n\n\n

\"\"<\/figure>\n","protected":false},"excerpt":{"rendered":"

I\u2019ve been generating reports to track our Microsoft Teams adoption \u2013 how many people are using Teams, how many messages are being sent in Teams, how many Teams are there. Some of these metrics have easily visualized count-per-unit-time summaries available. Some, like the number of Teams, do not. Team Created On Directory Services 1\/19\/2017 App …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[677],"tags":[675,709,729,731,265],"class_list":["post-4100","post","type-post","status-publish","format-standard","hentry","category-office-365","tag-did-you-know","tag-microsoft-excel","tag-microsoft-office-365","tag-ms-excel","tag-office-365"],"_links":{"self":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/4100","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=4100"}],"version-history":[{"count":1,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/4100\/revisions"}],"predecessor-version":[{"id":4106,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/4100\/revisions\/4106"}],"wp:attachment":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4100"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4100"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4100"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}