How to use the SUMIF and SUMIFS functions in Microsoft Excel and Google Sheets? A brief guide to conditional summing in spreadsheet programs

If you are dealing with a large set of data in an office, you must be using Microsoft Excel, Google Sheets, or any other similar type of program to get your data organized. While the function ‘SUM’ is a very common spreadsheet function we all have learned in our school days, it is highly limited, obviously because it just gives you the sum of some selected numbers in a specified range. But if you want something more, like a sum, that satisfies a condition, you can use the SUMIF function. 

If, on the other hand, you want the summation result of a set of numbers that satisfy multiple conditions, you should use the SUMIFS function. Both SUMIF and SUMIFS functions can be useful to calculate the sum in the case of raw data, and even in cases, when the data set is well-organized.

So, today I will talk about both the SUMIF and SUMIFS functions on Microsoft Excel or Google Sheets, as the syntax for the functions is the same across both platforms.

What is the SUMIF function on Microsoft Excel and Google Sheets?

The SUMIF is a function available on popular spreadsheet programs that are used to calculate the sum of values that fulfill certain criteria. An illustration will make the function clear for you.

The syntax of the SUMIF function goes as follows.

=sumif(criteria_range, input_criteria, sum_range)

Criteria_range: This is the range where the function will look for the criteria. Based on the values here, the actual sum will be calculated.

Input_criteria: The input given here will be searched in the criteria_range to select the values.

An illustration will make the function clear for you.

Sum_range: The sum of the values in this range will be calculated if the criteria are fulfilled.

Illustration of the SUMIF function

In the example given below, we have a sheet containing the name of some employees in an organization, their department, and their salary.

We have the names of the employees in column A, the name of the department they are working in column B, and their salaries in column C.

I have to calculate the salary expense of the organization across departments.

To calculate what I just said we have to list down the name of the departments in cells F8 to F13, and we will get the salary expense of the respective departments in column F in cells G8 to G13.

So, the formula in the first cell, i.e. cell G8 will go as follows.

=sumif(B2:B17,F8,C2:C17)

Here B2:B17 refers to the departments, which is the criterion for the SUMIF function.

The cell F8 refers to the key that will be matched with the values in the range B7:B17.

The sum of the values in the range C2:C17 will be calculated if the criteria are fulfilled.

Say for example, as per the formula in G8, it will look for the keyword in F8, which is IT. 

Here, the keyword IT will be searched in every cell starting B2 till B17. If there is a match, the corresponding cell in column C, i.e. C2, will be selected followed by subsequent other cells in column B if the entry matches the term ‘IT’. So, the values in cells C2, C5, and C8 will be added as they all have the term IT in the corresponding cells in column B.

In the next cells of column G, replace F8 with F9, F10, F11, and so on.

So, that’s how simple it is.

What is the SUMIFS function on Microsoft Excel and Google Sheets?

Now that you know about the SUMIF function, SUMIFS will look for multiple conditions, and only if all the conditions are fulfilled, the addition operation will be carried out. So, the SUMIFS function is an extended version of the SUMIF function. The syntax is a little different, but not hard to remember.

The syntax of the SUMIFS function goes as follows.

=SUMIFS(sum_range, criteria_range_1, criteria_1, … , criteria_range_n, criteria_n)

Sum_range: It is the range of values where the sum will be carried out if the conditions are fulfilled.

Criteria_range: It is the range where criteria will be looked for.

Criteria: It is the keyword that the SUMIFS function will search for in the criteria range.

Unlike the SUMIF function, you can have multiple ranges and criteria, and only if all the conditions are fulfilled, the addition in the specified range will be carried out.

Also, unlike the SUMIF function, in the SUMIFS function, the range of values supposed to be added will be at the beginning.

An illustration of the SUMIFS function

Here, just like the illustration for the SUMIF function, I have a similar sheet, but with an extra column having the cadre, i.e. executive and senior executive.

So, I will use the SUMIFS function to calculate the salary expense of the organization across departments and cadres.

So, if I want the salary expense of the organization for IT executives in cell K8, I have to keep the names of the departments in column I, and the cadres in column J. The formula in cell K8 will go as follows.

=sumifs(D2:D17,B2:B17,I8,C2:C17,J8)

Here, the SUMIFS function will find the criteria in cells I8 and J8. The criteria in cell I8 will be looked for in the range B2:B17, and the criteria in J8 will be matched in the range C2:C17. If the conditions are matched, the corresponding cells where both the conditions match, will be added.

The same will be done in the subsequent cells in column K, where I8 and J8 will be replaced with I9, J9, etc.

So, that’s all about the SUMIF and the SUMIFS function. In the case of large spreadsheets, if you use the SUMIF and SUMIFS functions, a lot of manual tasks can be minimized. Both the SUMIF and SUMIFS functions can also be integrated with other functions in Microsoft Excel or Google Sheets to make your tasks even simpler.

Hope you now get the idea of how to use the SUMIF and the SUMIFS function. Do you have any questions? Feel free to comment on the same below.