{"id":12466,"date":"2022-11-30T12:24:48","date_gmt":"2022-11-30T12:24:48","guid":{"rendered":"http:\/\/officetuts.net\/excel\/?p=12466"},"modified":"2024-03-26T11:06:23","modified_gmt":"2024-03-26T11:06:23","slug":"conditional-formatting-with-sumif-function","status":"publish","type":"post","link":"https:\/\/officetuts.net\/excel\/examples\/conditional-formatting-with-sumif-function\/","title":{"rendered":"Conditional Formatting with SUMIF Function in Excel"},"content":{"rendered":"\n
Conditional-Formatting-with-SUMIF-Function-in-Excel<\/a>Download File<\/a><\/div>\n\n\n\n

Conditional Formatting<\/strong> is one of the best methods for highlighting our data<\/strong> based on certain criteria. We can highlight the cells based on their value, or put a certain condition that the value in cells has to fulfill to be different from the rest of the cells in the range.<\/p>\n\n\n\n

In the example below, we will show how can we combine Conditional Formatting and SUMIF Function<\/strong>.<\/p>\n\n\n\n

Conditional Formatting Basics<\/h2>\n\n\n\n

First thing first, we will create the table with sales data that will consist of different people, the month in which the sales were achieved, and the sales that were achieved in a particular month:<\/p>\n\n\n\n

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

For the next thing, we are going to select all the data we have in column A<\/strong> (all the salesperson names) and then go to the Home tab >> Styles >> Conditional Formatting >> New Rule<\/strong>:<\/p>\n\n\n\n

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

Under the new window, we will choose the last option- Use a formula to determine which cells to format<\/strong>, and then define the following formula:<\/p>\n\n\n\n

=A2=$E$2<\/code><\/pre>\n\n\n\n
This is what our formula looks like in the Excel file:<\/p>\n\n\n\n
\"Graphical<\/figure>\n\n\n\n
The reason why we use cell $E$2<\/strong> is that we will use this cell to insert the names of different salespersons and to check their sales in cell F2<\/strong>. We will choose a proper format, in our case, it will be red background, by clicking Format<\/strong> and then choosing the appropriate color in the Fill tab<\/strong>:<\/p>\n\n\n\n
\"Graphical<\/figure>\n\n\n\n
When we click OK two times<\/strong>, and insert the name \u201cMargaret\u201d<\/strong> in cell E2<\/strong>, this is the result we will get in our table:<\/p>\n\n\n\n
\"Table\n\nDescription<\/figure>\n\n\n\n
Using SUMIF Function<\/h2>\n\n\n\n
Now we can use the SUMIF function<\/strong> to combine with the defined approach. Before that, we will define the Conditional Formatting for column C<\/strong> as well, to be dependable on the value in cell E2<\/strong>. We will use the same approach as before, and will insert a formula to determine the cells to format:<\/p>\n\n\n\n
\"Graphical<\/figure>\n\n\n\n
For the cells that are matching these conditions, we will fill them with blue color<\/strong>. We will click OK<\/strong> twice again, to finish this step.<\/p>\n\n\n\n
Our table will be changed, and it will look like this:<\/p>\n\n\n\n
\"Table\n\nDescription<\/figure>\n\n\n\n
In cell F2<\/strong>, we will now insert the SUMIF formula<\/strong>, which will encompass our table and the value in cell E2<\/strong>. This is the formula we will insert in cell F2<\/strong>:<\/p>\n\n\n\n
=SUMIF(A2:A17,E2,C2:C17)<\/code><\/pre>\n\n\n\n
SUMIF has three parameters<\/strong>: 1) Range<\/strong> (in our case range A2:A17<\/strong>)- the location where our value should be searched for; 2) Criteria<\/strong> (value in cell E2<\/strong>)- value that we need to search in the range; 3)<\/strong> Sum_range<\/strong> (in our case, that will be column C<\/strong>)- the range that we want to add if the conditions are met.<\/p>\n\n\n\n
Value in<\/strong> our cell F2 is $626,573<\/strong>, which is exactly the correct amount<\/strong> for all the sales that Margaret achieved in the first four months of the year:<\/p>\n\n\n\n
\"Graphical<\/figure>\n\n\n\n
All we need to do to find the total sales<\/strong> for the other salesperson is to change the name in cell E2<\/strong>. When we insert John<\/strong>, for example, total sales and formatting in our table will be adjusted:<\/p>\n\n\n\n
\"Graphical<\/figure>\n\n\n\n
Using SUMIFS Function<\/h2>\n\n\n\n
We can also use the SUMIFS formula<\/strong> to extract more useful data from our table. The structure of the SUMIFS sentence<\/strong> is a bit different than for SUMIF<\/strong>.<\/p>\n\n\n\n
SUMIFS formula<\/strong> will be inserted in cell G2<\/strong>, and we will define the following parameters: 1)<\/strong> Range C2:C17 as a sum_range<\/strong>; 2)<\/strong> First criteria range<\/strong> will be column A<\/strong>; 3)<\/strong> Criteria<\/strong> for the first range will be the value in cell E2<\/strong>; 4) Second criteria range<\/strong> will be the sales figures in column C<\/strong>; 5) Criteria<\/strong> for the second range will be \u201c>200000\u201d<\/strong> which means that we only want to show the sales figures over $200,000<\/strong> for every person on a monthly level.<\/p>\n\n\n\n
We will use Rachel\u2019s data (insert her name in cell E2<\/strong>), and this is what we will end up with:<\/p>\n\n\n\n
\"Graphical<\/figure>\n\n\n\n
Although we have every figure for Rachel highlighted, the formula in cell G2<\/strong> will omit the data in row number eight<\/strong>, as the figures in this row are smaller <\/strong>than the desired number ($200,000)<\/strong>.<\/p>\n","protected":false},"excerpt":{"rendered":"
Conditional Formatting is one of the best methods for highlighting our data based on certain criteria. We can highlight the cells based on…<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nConditional Formatting with SUMIF Function in Excel<\/title>\n<meta name=\"description\" content=\"Conditional Formatting with SUMIF in Excel file to learn how to highlight data based on specific criteria and combine Conditional Formatting and SUMIF.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/officetuts.net\/excel\/examples\/conditional-formatting-with-sumif-function\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Conditional Formatting with SUMIF Function in Excel\" \/>\n<meta property=\"og:description\" content=\"Conditional Formatting with SUMIF in Excel file to learn how to highlight data based on specific criteria and combine Conditional Formatting and SUMIF.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/officetuts.net\/excel\/examples\/conditional-formatting-with-sumif-function\/\" \/>\n<meta property=\"article:published_time\" content=\"2022-11-30T12:24:48+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-26T11:06:23+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2022\/11\/graphical-user-interface-table-description-autom-5.png\" \/>\n<meta name=\"author\" content=\"Harun Spahic\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Harun Spahic\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/conditional-formatting-with-sumif-function\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/conditional-formatting-with-sumif-function\/\"},\"author\":{\"name\":\"Harun Spahic\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/694491c70f776081acdac4ae41f222a2\"},\"headline\":\"Conditional Formatting with SUMIF Function in Excel\",\"datePublished\":\"2022-11-30T12:24:48+00:00\",\"dateModified\":\"2024-03-26T11:06:23+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/conditional-formatting-with-sumif-function\/\"},\"wordCount\":681,\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/conditional-formatting-with-sumif-function\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2022\/11\/graphical-user-interface-table-description-autom-5.png\",\"keywords\":[\"file\",\"pinterest\"],\"articleSection\":[\"examples\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/conditional-formatting-with-sumif-function\/\",\"url\":\"https:\/\/officetuts.net\/excel\/examples\/conditional-formatting-with-sumif-function\/\",\"name\":\"Conditional Formatting with SUMIF Function in Excel\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/conditional-formatting-with-sumif-function\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/conditional-formatting-with-sumif-function\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2022\/11\/graphical-user-interface-table-description-autom-5.png\",\"datePublished\":\"2022-11-30T12:24:48+00:00\",\"dateModified\":\"2024-03-26T11:06:23+00:00\",\"description\":\"Conditional Formatting with SUMIF in Excel file to learn how to highlight data based on specific criteria and combine Conditional Formatting and SUMIF.\",\"breadcrumb\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/conditional-formatting-with-sumif-function\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/officetuts.net\/excel\/examples\/conditional-formatting-with-sumif-function\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/conditional-formatting-with-sumif-function\/#primaryimage\",\"url\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2022\/11\/graphical-user-interface-table-description-autom-5.png\",\"contentUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2022\/11\/graphical-user-interface-table-description-autom-5.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/conditional-formatting-with-sumif-function\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/officetuts.net\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Conditional Formatting with SUMIF Function in Excel\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/officetuts.net\/excel\/#website\",\"url\":\"https:\/\/officetuts.net\/excel\/\",\"name\":\"\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/officetuts.net\/excel\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\",\"name\":\"Tomasz Decker\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"caption\":\"Tomasz Decker\"},\"logo\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\"},\"description\":\"Spreadsheet and Python enthusiast.\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/694491c70f776081acdac4ae41f222a2\",\"name\":\"Harun Spahic\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/b7b887a2249679182be5550a4421a7a9?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/b7b887a2249679182be5550a4421a7a9?s=96&d=mm&r=g\",\"caption\":\"Harun Spahic\"},\"sameAs\":[\"http:\/\/human.bsru.ac.th\/\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Conditional Formatting with SUMIF Function in Excel","description":"Conditional Formatting with SUMIF in Excel file to learn how to highlight data based on specific criteria and combine Conditional Formatting and SUMIF.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/officetuts.net\/excel\/examples\/conditional-formatting-with-sumif-function\/","og_locale":"en_US","og_type":"article","og_title":"Conditional Formatting with SUMIF Function in Excel","og_description":"Conditional Formatting with SUMIF in Excel file to learn how to highlight data based on specific criteria and combine Conditional Formatting and SUMIF.","og_url":"https:\/\/officetuts.net\/excel\/examples\/conditional-formatting-with-sumif-function\/","article_published_time":"2022-11-30T12:24:48+00:00","article_modified_time":"2024-03-26T11:06:23+00:00","og_image":[{"url":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2022\/11\/graphical-user-interface-table-description-autom-5.png"}],"author":"Harun Spahic","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Harun Spahic","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/officetuts.net\/excel\/examples\/conditional-formatting-with-sumif-function\/#article","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/examples\/conditional-formatting-with-sumif-function\/"},"author":{"name":"Harun Spahic","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/694491c70f776081acdac4ae41f222a2"},"headline":"Conditional Formatting with SUMIF Function in Excel","datePublished":"2022-11-30T12:24:48+00:00","dateModified":"2024-03-26T11:06:23+00:00","mainEntityOfPage":{"@id":"https:\/\/officetuts.net\/excel\/examples\/conditional-formatting-with-sumif-function\/"},"wordCount":681,"publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"image":{"@id":"https:\/\/officetuts.net\/excel\/examples\/conditional-formatting-with-sumif-function\/#primaryimage"},"thumbnailUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2022\/11\/graphical-user-interface-table-description-autom-5.png","keywords":["file","pinterest"],"articleSection":["examples"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/officetuts.net\/excel\/examples\/conditional-formatting-with-sumif-function\/","url":"https:\/\/officetuts.net\/excel\/examples\/conditional-formatting-with-sumif-function\/","name":"Conditional Formatting with SUMIF Function in Excel","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/#website"},"primaryImageOfPage":{"@id":"https:\/\/officetuts.net\/excel\/examples\/conditional-formatting-with-sumif-function\/#primaryimage"},"image":{"@id":"https:\/\/officetuts.net\/excel\/examples\/conditional-formatting-with-sumif-function\/#primaryimage"},"thumbnailUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2022\/11\/graphical-user-interface-table-description-autom-5.png","datePublished":"2022-11-30T12:24:48+00:00","dateModified":"2024-03-26T11:06:23+00:00","description":"Conditional Formatting with SUMIF in Excel file to learn how to highlight data based on specific criteria and combine Conditional Formatting and SUMIF.","breadcrumb":{"@id":"https:\/\/officetuts.net\/excel\/examples\/conditional-formatting-with-sumif-function\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/officetuts.net\/excel\/examples\/conditional-formatting-with-sumif-function\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/examples\/conditional-formatting-with-sumif-function\/#primaryimage","url":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2022\/11\/graphical-user-interface-table-description-autom-5.png","contentUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2022\/11\/graphical-user-interface-table-description-autom-5.png"},{"@type":"BreadcrumbList","@id":"https:\/\/officetuts.net\/excel\/examples\/conditional-formatting-with-sumif-function\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/officetuts.net\/excel\/"},{"@type":"ListItem","position":2,"name":"Conditional Formatting with SUMIF Function in Excel"}]},{"@type":"WebSite","@id":"https:\/\/officetuts.net\/excel\/#website","url":"https:\/\/officetuts.net\/excel\/","name":"","description":"","publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/officetuts.net\/excel\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42","name":"Tomasz Decker","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","caption":"Tomasz Decker"},"logo":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/"},"description":"Spreadsheet and Python enthusiast."},{"@type":"Person","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/694491c70f776081acdac4ae41f222a2","name":"Harun Spahic","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/b7b887a2249679182be5550a4421a7a9?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/b7b887a2249679182be5550a4421a7a9?s=96&d=mm&r=g","caption":"Harun Spahic"},"sameAs":["http:\/\/human.bsru.ac.th\/"]}]}},"_links":{"self":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/12466"}],"collection":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/comments?post=12466"}],"version-history":[{"count":4,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/12466\/revisions"}],"predecessor-version":[{"id":15396,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/12466\/revisions\/15396"}],"wp:attachment":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/media?parent=12466"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/categories?post=12466"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/tags?post=12466"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}