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.

**On this Page**show

## 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.

## Related Posts

## Adding Visual Studio Code to the System Path in Windows 11 or 10

## Finding Visual Studio Code Version on Windows 11 or 10

## Running PHP Files in Visual Studio Code with XAMPP: A Step-by-Step Guide

## How to do a scatter plot in Google Sheets? Easily represents the correlation between numerical values

## Multiple Methods to Verify Python Installation on Windows 11

## How to Install Pygame Zero on Windows 11