SUMIFS Help Please

iLoveMexico

Hi, I need help for a simple formula.  I emailed Smartsheet support a month ago but never got an answer.

I want to total how much has been spent by budget type in a given year.  Example types are Gardening or Security.  Debit is the $ amount, and Year is 2019 in this case.

Columns are:   Type, Debit and year.

Can anyone please help?  Thanks very much!

Lunda

AYarrington

Hi Lunda!

You'll want to designate a spot on your sheet to keep the formulas (like perhaps the top rows of the sheet) OR put them on a separate sheet. Let's assume you're going to put them at the top of the same sheet. 

1. Insert a blank row at the top of your sheet, above your data, then in the Type column enter one of your categories, such as "Gardening". Enter the year value in the Year column. We are setting up the criteria to use in your SUMIFS formula.
2. In the Debit column enter your formula. This will look like "=SUMIFS(Debit2:Debit99,Type2:Type99,Type1,Year2:Year99,Year1)"

I am assuming here that your actual data lives in rows 2 through 99, however you'll need to adjust these row numbers depending on how many rows of data your sheet actually has and where the data starts. If you add many summary total rows to the top of your sheet, your actual data might not start until row 10, for example.

Paul Newcome

I personally would suggest using a separate sheet for your totals and building it out similar to a table.Something along the lines of...

.

Type                Debit

2019

Gardening           f

Security               f

Other                   f

.

You could then use a formula like this:

 

=SUMIFS({Master Sheet Debit Range}, {Master Sheet Type Range}, Type@row, {Master Sheet Year Range}, Type$1)

.

{Master Sheet Debit Range}: Use the appropriate steps for referencing another sheet and select the Debit column from your original sheet.

{Master Sheet Type Range}: Same as above except select the Type column.

Type@row: Leave as is. References the text that is in they Type column of your Totals sheet in whatever row the formula is residing on.

{Master Sheet Year Range}: Same as other ranges except select the column from your original sheet that has the year in it.

Type$1: References the cell in row 1 of the Type column in your Totals sheet. This houses the year you are looking for. NOTE: The $ is important because it locks in the row 1 reference which allows you to be able to dragfill.

.

Using a separate sheet keeps things looking cleaner overall and allows you to select entire columns for the ranges to be summed without running into a Circular Reference error. This allows new rows to be added without having to adjust formula ranges.