{"id":5057,"date":"2019-04-03T16:19:49","date_gmt":"2019-04-03T21:19:49","guid":{"rendered":"http:\/\/lisa.rushworth.us\/?p=5057"},"modified":"2019-04-05T15:08:19","modified_gmt":"2019-04-05T20:08:19","slug":"did-you-know-excel-can-create-a-sum-values-where-a-condition-is-true","status":"publish","type":"post","link":"https:\/\/www.rushworth.us\/lisa\/?p=5057","title":{"rendered":"Did you know \u2026 Excel can create a sum of values where a condition is true?"},"content":{"rendered":"<p>For a project, we need to divide the entire company into groups. I chose organizational structure because it\u2019s easy \u2013 I can determine the reporting structure for any employee or contractor, and I can roll people into groups under which ever level of manager I want.<\/p>\n<p>The point of making groups, though, is to have <em>close<\/em> to the same number of people in each group. While I can use <a href=\"https:\/\/windstream.jiveon.com\/community\/information-technology\/it-essentials-for-employees\/blog\/2018\/12\/18\/did-you-know-you-can-use-microsoft-excel-to-count-the-number-of-records-within-a-range\">COUNTIFS<\/a> to count the number of people who report up through each manager, I need to add those totals for each <em>group<\/em> of managers to determine how many individuals fall in each group. How many employees are included in Group 0?<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"1127\" height=\"578\" class=\"wp-image-5058\" src=\"http:\/\/lisa.rushworth.us\/wp-content\/uploads\/2019\/04\/word-image.png\" srcset=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/04\/word-image.png 1127w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/04\/word-image-300x154.png 300w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/04\/word-image-768x394.png 768w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/04\/word-image-1024x525.png 1024w\" sizes=\"auto, (max-width: 1127px) 100vw, 1127px\" \/><\/p>\n<p>This is actually quite easy \u2013 just like count has a conditional counterpart, countifs, <em>sum<\/em> has a conditional counterpart <a href=\"https:\/\/support.office.com\/en-us\/article\/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b\"><em>sumifs<\/em><\/a><\/p>\n<p>The usage is =SUMIFS( Range Of Data To Sum, Range Of Data Where Criterion Needs To Match, Criterion That Needs To Match)<\/p>\n<p>You can use multiple criteria ranges and corresponding criteria in your conditional sum &#8212; =SUMIFS(SumRange,CriterionRange1,CriterionMatch1,CriterionRange2,CriterionMatch2,\u2026,CriterionRangeN,CriterionMatchN).<\/p>\n<p>I only have one condition, so with a quick listing of the groups, I can add a column that tells me how many individuals are included in each group.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"399\" height=\"299\" class=\"wp-image-5059\" src=\"http:\/\/lisa.rushworth.us\/wp-content\/uploads\/2019\/04\/word-image-1.png\" srcset=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/04\/word-image-1.png 399w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/04\/word-image-1-300x225.png 300w\" sizes=\"auto, (max-width: 399px) 100vw, 399px\" \/><\/p>\n<p><strong><em>Bonus did you know \u2013 <\/em><\/strong>instead of specifying a start and end cell for a range, you can use the entire column. Instead of saying my \u201cRange of data to sum\u201d is B2:B101, I just used B:B to select the <em>entire<\/em> \u201cB\u201d column.<\/p>\n<p>Viewing the values, I can see that my group size is not consistent.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"297\" height=\"316\" class=\"wp-image-5060\" src=\"http:\/\/lisa.rushworth.us\/wp-content\/uploads\/2019\/04\/word-image-2.png\" srcset=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/04\/word-image-2.png 297w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/04\/word-image-2-282x300.png 282w\" sizes=\"auto, (max-width: 297px) 100vw, 297px\" \/><\/p>\n<p>As I adjust the group to which the manager is assigned, these sums are updated in real-time.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"273\" height=\"294\" class=\"wp-image-5061\" src=\"http:\/\/lisa.rushworth.us\/wp-content\/uploads\/2019\/04\/word-image-3.png\" \/><\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>For a project, we need to divide the entire company into groups. I chose organizational structure because it\u2019s easy \u2013 I can determine the reporting structure for any employee or contractor, and I can roll people into groups under which ever level of manager I want. The point of making groups, though, is to have &hellip;<\/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,708,709,691,729,731],"class_list":["post-5057","post","type-post","status-publish","format-standard","hentry","category-office-365","tag-did-you-know","tag-excel","tag-microsoft-excel","tag-microsoft-office","tag-microsoft-office-365","tag-ms-excel"],"_links":{"self":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/5057","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=5057"}],"version-history":[{"count":2,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/5057\/revisions"}],"predecessor-version":[{"id":5104,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/5057\/revisions\/5104"}],"wp:attachment":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5057"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5057"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5057"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}