sum_range<\/b> is an optional parameter that specifies the cells to add up. If left out, the default is to add up the cells in the range that meet the criteria<\/li>\n<\/ul>\n[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
How to use the SUMIF Excel function<\/h3>\n
I\u2019ll show some examples to demonstrate how to use the SUMIF Excel function to add up data in any way you want.<\/p>\n
In the examples, I use data from a fictional department store, with sales numbers for different employees in various departments.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Critera with logical operator<\/h4>\n
Take a look at the first example below. I want to find the total number of sales by all employees who had more than $10,000 in sales.<\/p>\n
Right away, you can see that I can\u2019t simply add up the sales numbers in column C. Doing this would include the sales of employees who had less than $10,000 in sales.<\/p>\n
I could manually select all the sales numbers that fit my criteria, but this would be time-consuming, especially in a large dataset. So instead, I type =SUMIF(C2:C11,”>10000″)<\/b>.[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/1.-SUMIF-Logical-operator-example.png” alt=”sumif excel 1″ title_text=”1. SUMIF Logical operator example” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
I use the greater than “>”<\/b> logical operator in this example, but you can use any of the logical operators shown below.<\/p>\n
Just make sure to put the criteria in quotation marks when using a logical operator.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/2.-SUMIF-Logical-Operators.png” alt=”sumif excel logical operators 2″ title_text=”2. SUMIF Logical Operators” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Critera as text string<\/h4>\n
In the next example, I\u2019ll show a different type of criteria. This time, I\u2019m filtering the data based on the department in which the employee works.<\/p>\n
Specifically, I want to determine the total sales of employees in the Food department. I achieve this with the formula =SUMIF(B2:B11,”Food”,C2:C11)<\/b>.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/3.-SUMIF-text-string-example.png” alt=”sumif excel text string 3″ title_text=”3. SUMIF text string example” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
There\u2019s a couple of differences between these two examples. Firstly, the criteria in the second example is not numerical \u2013 it\u2019s a text string. The function searches the range for the string \u201cFood\u201d and adds up all the sales of the corresponding cells.<\/p>\n
That brings me to the second difference: because I\u2019m adding up data from a different column than where I\u2019m applying the criteria, I have to use the sum_range<\/b> parameter.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Criteria as cell reference & multiple SUMIFs<\/h4>\n
Now I\u2019ll show another way to specify your criteria: using a cell reference. Instead of typing out the department I want, I\u2019ve just selected a cell that contains that department as my criteria parameter. This saves a bit of time compared to typing out the text string.<\/p>\n
You\u2019ll notice that I\u2019m also using two separate SUMIF functions added together. The reason for that is in this example, I wish to find the total sales of the clothing and hardware departments. I can simply add two SUMIF functions together to achieve this, with the formula =SUMIF(B2:B11,B6,C2:C11) + SUMIF(B2:B11,B8,C2:C11)<\/b>.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/4.-SUMIF-cell-reference-example.png” alt=”sumif excel cell reference 4″ title_text=”4. SUMIF cell reference example” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Concatenated criteria<\/h4>\n
Now I\u2019ll try something a bit more fancy. In the first example, I showed how to use logical operators to specify which cells to add. I\u2019ll do the same thing here, but with a combination of a logical operator and a cell reference combined using concatenation.<\/p>\n
This is useful in a spreadsheet that you\u2019re using dynamically \u2013 for instance, if you want to quickly change the criteria by changing one cell, and have the function adjust automatically.<\/p>\n
In this example, I want to find the total sales of all employees who had less than $9,000 in sales, but I want the spreadsheet to be dynamic so I can easily put a new number in cell F1 to change the criteria. I do this with the formula =SUMIF(C2:C11,”<\"&F1)<\/b>.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/5.-SUMIF-Concatenate-example.png” alt=”sumif excel concatenate 5″ title_text=”5. SUMIF Concatenate example” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Notice how I concatenated (combined) the F1 cell with the criteria in the function by using the & sign. Also note that I had to put quotation marks around the < sign, but not around the rest of the criteria.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Wildcards<\/h4>\n
By now, you hopefully understand the basics of how the SUMIF function in Excel works. Which means it\u2019s a good time for me to introduce wildcards.<\/p>\n
You can use the wildcard characters * and ? within your criteria to indicate a match with any character (?) or any sequence of characters (*).<\/p>\n
I\u2019ll first give an example with the * wildcard. As I mentioned above, this wildcard is a stand-in for any characters. So if my criteria<\/b> was just *, then any cells with data in them would qualify (only blank cells would be left out). That\u2019s a good way to eliminate blanks in your dataset.<\/p>\n
Another good use of the wildcard is shown in the example below. In this case, I want to get the total sales from all employees whose names end with \u201cy\u201d.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/6.-SUMIF-_-example.png” alt=”sumif excel 6″ title_text=”6. SUMIF _ example” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
With the formula =SUMIF(A2:A11,”*y”,C2:C11)<\/b> I instruct Excel to add up all cells that meet the criteria<\/b> of having any characters followed by a \u201cy\u201d. Note that the * wildcard represents any number of characters, so it doesn\u2019t matter how long the name is as long as it ends in \u201cy\u201d.<\/p>\n
Now I\u2019ll contrast that with the ? wildcard. Similar to the * wildcard, this one is a stand-in for any character. The difference is that the ? can only represent a single character.<\/p>\n
This example is a bit strange, but imagine I want the sales numbers from only the departments that end in \u201cs\u201d. On top of that, the department names also have to be 11 letters long. So instead of using the * wildcard, I use ? by typing =SUMIF(B2:B11,”??????????s”,C2:C11)<\/b>.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/7.-SUMIF-_-example.png” alt=”sumif excel 7″ title_text=”7. SUMIF _ example” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
As I mentioned before, you can use the * wildcard to filter based on blank cells. I\u2019ll demonstrate that in the screenshot below.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/8.-SUMIF-blanks-example.png” alt=”sumif excel blanks 8″ title_text=”8. SUMIF blanks example” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
By typing =SUMIF(B2:B11,”<>*”,C2:C11)<\/b> I tell Excel to add up the data for all the cells that are blank. This can be handy in cases where the dataset is incomplete.[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\nThanks for reading!<\/h3>\n
I hope you now have a good understanding of how you can use the Excel SUMIF function to customize your spreadsheet and use a simple formula to save you time!<\/p>\n
If you\u2019d like to learn more about the various formulas of Google Sheets, why not take a look at our blog post on the DSUM formula in Google Sheets.<\/a><\/span><\/p>\n[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":"
People often use Microsoft Excel for highly sophisticated and complex calculations. At other times, you may just want to use Excel to add up certain data points. While the SUM function is perfect for doing just that, what happens when you don\u2019t want to tally all the data in your spreadsheet? In that case, you […]<\/p>\n","protected":false},"author":40,"featured_media":31267,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[65],"tags":[39,48,28],"class_list":["post-31221","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-functions","tag-connections-t","tag-excel","tag-spreadsheets"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts\/31221","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/users\/40"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/comments?post=31221"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts\/31221\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/media\/31267"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/media?parent=31221"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/categories?post=31221"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/tags?post=31221"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}