Apps and Software

Using The Sumifs Function to Replace Filtering In Excel Spreadsheets

Sumifs excel, xls, xlsx

What is the Sumifs function in excel and who uses it?

SUMIFS function is a versatile Excel function that, with a little creativity, can increase your workflow and performance with minimal effort. You do not need to be an expert to make effective use of Excel. Chances are, if you are not already an expert – you do not need to be.

Sumifs excel, xls, xlsx
Photo by swissmith on Pixabay

The SUMIFS function is an intermediate level function that requires a basic understanding of how Excel works to be used effectively. Take a look at the function:

= SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

The SUMIFS function adds together all arguments within a set range that meet a set of criteria provided by the user.

=SUMIFS(A:A, B:B,critera 1, D:D, criteria 2)

In the above example, each row in column “A” will be added to a total only when the data in both column “B” and column “D” match what you have specified. SUMIFS does not require that the data ranges fall into the same rows, but they must have the same number of rows for the function to work.

If you are currently using filters to collect sums, SUMIFS can reduce the time you spend filtering data. By telling the function to find criteria based on the contents of specific cells you can then manipulate the sum you receive by entering the filter criteria into the cells you have defined.

=SUMIFS(A:A:, B:B, C1, D:D, C2,)

By defining the criteria as cells C1 and C2, you can collect sums for column A without having to adjust filters or rewrite the function. The criteria defined in these cells may consist of numbers, letters, wild cards, functions, or cell references. If you sometimes require variable criteria sets, SUMIFS accepts an asterisk (*) as a wild card. Using a wildcard can reduce the criteria needed for the function to execute by telling the function to bypass requirements for criteria marked with an asterisk. With a bit of creativity, you can daisy-chain several functions to drastically cut back on time spent analyzing data.

If you have a basic understanding of how Excel works then you are ready to begin using intermediate functions. SUMIFS is a simple but versatile tool that can reduce the amount of time and effort spent analyzing and manipulating tables and spreadsheets.

Leave a Reply

Your email address will not be published. Required fields are marked *