Unable to get SUMIFS to work

Joshua Tilander

Hello, I am trying to use the SUMIFS function to total up the expenditure data from an imported file. I want the cell to total all of the expenditures for a particular district based on fund type, but am not successful. I am wanting to pull the data from another sheet called "Allocations" which has all of the information I need to tabulate. Additionally, I want the cell to bring up the values that meet the criteria below: give me the expenditures for district 1 (Eureka), PEC Class 4020, Object Class 0199, and fall in fund 0042. I keep getting an #invalid argument using the formula shown.

Kelly Moore

Hello @Joshua Tilander

The column you are trying to sum- AP Allocations Range 7- is it a column that contains numeric data? SUMIFs requires this. If you are wanting a total count, COUNTIFs would be the function to use and doesn't have a requirement of using a numeric field. Also, you may experience an error if no data meets the criteria listed.

Joshua Tilander

@KDM The range you are referring to does have numeric data.

Kelly Moore

@Joshua Tilander

The syntax of your SUMIFs appears correct. When I am troubleshooting a formula, I begin eliminating the criteria within a function one by one to see if the data I'm trying to filter is causing the error. I also double check every cross functional range I have listed to ensure that the range is actually the column I intended (as a good practice, consider renaming your {ranges} from generic range numbers to actual column names to better help you trouble shoot).

I've also been fooled with data I thought was numeric but in reality smartsheet considered it text that looked like a number. In the source sheet, in some available text/number column or temporary helper column you could test your Sum column using the formula =IF(ISNUMBER([your real column name of the sum column]@row), "true", "false"). This checks whether smartsheet sees the data as numeric or not. If your column name doesn't include spaces, numbers or special characters you would not need the square brackets around the column name. This is only a temporary formula for troubleshooting which you can delete from your source sheet after you do the test.