Sumifs

Using SUMIFS functions are an great way to total your data based on specific criteria. In the workbook Where'd It Go? - Earnings and Spending Tracker a group of SUMIFS are used to gather the data from each of the bank account sheets. Here we will look at the formula and break it into smaller pieces. We will discuss what each part of the function does, and examine how multiple functions are used to create the main formula.

Create your own copy of 'Where'd It Go?'

Click the button above to create your own copy of Where'd It Go? - Earnings and Spending Tracker.

The main purpose of the SUMIFS functions used in 'Where'd It Go?' are to add up all the transaction for each Spending and Earnings Category, based on the date of the transaction.

The formula contains 8 lines, we can ignore the first line for now (the IF and ISBLANK functions will be covered elsewhere), leaving us with 7 lines to look at. In this example, each line is a separate SUMIFS function. The operator (+/-) to the left of the line denotes whether that line is added to, or subtracted from, the total.

=if(isblank($A13), ,
+sumifs('Input - Bank Acct 1'!$C:$C,'Input - Bank Acct 1'!$F:$F,$A13,'Input - Bank Acct 1'!$H:$H,month(C$3),'Input - Bank Acct 1'!$I:$I,year(C$3))
-sumifs('Input - Bank Acct 1'!$D:$D,'Input - Bank Acct 1'!$F:$F,$A13,'Input - Bank Acct 1'!$H:$H,month(C$3),'Input - Bank Acct 1'!$I:$I,year(C$3))
+sumifs('Input - Bank Acct 2'!$C:$C,'Input - Bank Acct 2'!$F:$F,$A13,'Input - Bank Acct 2'!$H:$H,month(C$3),'Input - Bank Acct 2'!$I:$I,year(C$3))
-sumifs('Input - Bank Acct 2'!$D:$D,'Input - Bank Acct 2'!$F:$F,$A13,'Input - Bank Acct 2'!$H:$H,month(C$3),'Input - Bank Acct 2'!$I:$I,year(C$3))
+sumifs('Input - Credit Card 1'!$C:$C,'Input - Credit Card 1'!$F:$F,$A13,'Input - Credit Card 1'!$H:$H,month(C$3),'Input - Credit Card 1'!$I:$I,year(C$3))
-sumifs('Input - Credit Card 1'!$D:$D,'Input - Credit Card 1'!$F:$F,$A13,'Input - Credit Card 1'!$H:$H,month(C$3),'Input - Credit Card 1'!$I:$I,year(C$3))
+sumifs('Split Transactions'!$C:$C,'Split Transactions'!$B:$B,$A13,'Split Transactions'!$G:$G,month(C$3),'Split Transactions'!$H:$H,year(C$3)))

We are going to focus on a single line for now, and then learn how they work together. Here is the line we will use:

=sumifs('Input - Bank Acct 1'!$C:$C,'Input - Bank Acct 1'!$F:$F,$A13,'Input - Bank Acct 1'!$H:$H,month(C$3),'Input - Bank Acct 1'!$I:$I,year(C$3))

The SUMIFS function contains 3 values, 2 of which are repeatable. This is where the IFS in SUMIFS comes from, multiple conditions can be imposed on the data before it is summed.

=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, …], [criterion2, …])

Let's look at just a single iteration of the SUMIFS function, with no repetition. Here is our example with a single criteria_range and criterion:

=sumifs('Input - Bank Acct 1'!$C:$C,'Input - Bank Acct 1'!$F:$F,$A13

The sum_range is the data you want added or subtracted. In the example from 'Where'd It Go?', the sum_range is the entire C column ($C:$C) from the Input - Bank Acct 1 worksheet. In this case, that is the Withdrawals column from the sheet where transactions are recorded.

The range selected for criteria_range1 determines whether the data in the same row of the sum_range should be included in the calculation. In the example, criteria_range1 is the F column ($F:$F) from the same worksheet, Input - Bank Acct 1. On the sheets used to record transactions, the F column is the Spending or Earnings Category for the transaction. The Category is selected when transactions are entered, and it is how all spending and earnings are classified.

Finally, criterion1 is the value to look for in criteria_range1. In this case it is the data in cell A13 ($A13), which is the Spending or Earnings Category (Bank Fee in the screenshot) we are finding the total for.

To wrap up our single criteria_range and criterion example, we are looking for a Spending or Earnings Category ($A13) in our criteria_range ('Input - Bank Acct 1'!$F:$F). If our Category is found, the Withdrawals column ('Input - Bank Acct 1'!$C:$C) is added to the sum.

Now we can examine the repeating criteria_range and criterion. We will use the second part of our example function, here is the full function:

=sumifs('Input - Bank Acct 1'!$C:$C,'Input - Bank Acct 1'!$F:$F,$A13,'Input - Bank Acct 1'!$H:$H,month(C$3),'Input - Bank Acct 1'!$I:$I,year(C$3))

Here is the portion of the function which contains criteria_range2, criterion2, criteria_range3, and criterion3:

'Input - Bank Acct 1'!$H:$H,month(C$3),'Input - Bank Acct 1'!$I:$I,year(C$3))

What we sum (sum_range) remains the same, only what to look for changes with each iteration.

Our example shows criteria_range2 as the H column on the same transaction input worksheet ('Input - Bank Acct 1'!$H:$H ). This column displays the month each transaction occurred.

The value for criterion2 uses the MONTH function, which finds the month from a cell (C$3). In the screenshot, row 3 contains the date in MM/YYYY format for each month, with C$3 being January.

Similarly, criteria_range3 is a column that contains the year of each transaction ('Input - Bank Acct 1'!$I:$I).

Lastly, criterion3 uses the YEAR function to find the year from a cell (C$3). Our example is the cell C$3, which is 2022.

Let's put our function together and see how it all works:

=sumifs('Input - Bank Acct 1'!$C:$C,'Input - Bank Acct 1'!$F:$F,$A13,'Input - Bank Acct 1'!$H:$H,month(C$3),'Input - Bank Acct 1'!$I:$I,year(C$3))

We want to sum the Withdrawals column ('Input - Bank Acct 1'!$C:$C), if the Spending or Earnings Category ($A13) is found our criteria_range1 ('Input - Bank Acct 1'!$F:$F), and if the month ('Input - Bank Acct 1'!$H:$H) and year ('Input - Bank Acct 1'!$I:$I) columns match the date in the column header (C$3).

Now that we have seen how a single line of the formula, looking at the 7 lines containing SUMIFS functions should be easier.

=if(isblank($A13), ,
+sumifs('Input - Bank Acct 1'!$C:$C,'Input - Bank Acct 1'!$F:$F,$A13,'Input - Bank Acct 1'!$H:$H,month(C$3),'Input - Bank Acct 1'!$I:$I,year(C$3))
-sumifs('Input - Bank Acct 1'!$D:$D,'Input - Bank Acct 1'!$F:$F,$A13,'Input - Bank Acct 1'!$H:$H,month(C$3),'Input - Bank Acct 1'!$I:$I,year(C$3))
+sumifs('Input - Bank Acct 2'!$C:$C,'Input - Bank Acct 2'!$F:$F,$A13,'Input - Bank Acct 2'!$H:$H,month(C$3),'Input - Bank Acct 2'!$I:$I,year(C$3))
-sumifs('Input - Bank Acct 2'!$D:$D,'Input - Bank Acct 2'!$F:$F,$A13,'Input - Bank Acct 2'!$H:$H,month(C$3),'Input - Bank Acct 2'!$I:$I,year(C$3))
+sumifs('Input - Credit Card 1'!$C:$C,'Input - Credit Card 1'!$F:$F,$A13,'Input - Credit Card 1'!$H:$H,month(C$3),'Input - Credit Card 1'!$I:$I,year(C$3))
-sumifs('Input - Credit Card 1'!$D:$D,'Input - Credit Card 1'!$F:$F,$A13,'Input - Credit Card 1'!$H:$H,month(C$3),'Input - Credit Card 1'!$I:$I,year(C$3))
+sumifs('Split Transactions'!$C:$C,'Split Transactions'!$B:$B,$A13,'Split Transactions'!$G:$G,month(C$3),'Split Transactions'!$H:$H,year(C$3)))

Each line is searching for the same Spending or Earnings Category ($A13) and they all use the MONTH and YEAR functions to confirm the transaction occurred in the proper period.

There are 4 differences from line to line, the 3 ranges in the SUMIFS function and the operators used.

The sum_range changes from the Withdrawals column ('Input - Bank Acct 1'!$C:$C) to the Deposits column ('Input - Bank Acct 1'!$D:$D), along with the operator (+/-). In our example, the Spending Category ($A13) is Bank Fee, so the Withdrawals columns are added and the Deposits columns are subtracted.

The criteria_range values change, and the entire formula will search each sheet in the workbook. The workbook has two bank account sheets ('Input - Bank Acct 1' & 'Input - Bank Acct 2'), one credit card account sheet ('Input - Credit Card 1'), and one sheet for splitting transactions ('Split Transactions').

If this example were an Earnings Category, instead of a Spending Category, the operators (+/-) would be reversed for each line, as you would be adding the Deposits and subtracting Withdrawals.