Help with IFS formula

owenhpdx

I have a Smartsheet that tracks all grant applications in my department. Among other columns are "Application Due Date" and "Total Award Requested". I would like to be able to report on the sum of all funds applied for during a fiscal year.

The AI formula creation tool produced the formula below. It looks like it should be correct, but the result is FAR lower than the sum total of all figures in the "Total Award Requested" column for applications submitted between 7/1/2023 and 6/30/2024. Can someone help me understand why the formula isn't working properly?

=SUMIFS([Total Award Requested]:[Total Award Requested], [Application Due Date]:[Application Due Date], >=DATE(2023, 7, 1), [Application Due Date]:[Application Due Date], <=DATE(2024, 6, 30))

Thanks,

Heidi

Carson Penticuff

The formula seems correct for what you are attempting to do. Is it possible some of the date cells are not actually formatted as dates? You can plug this formula in temporarily to see how many rows Smartsheet believes match your criteria. If this doesn't match a manual count, there is a date formatting issue somewhere.

=COUNTIFS([Application Due Date]:[Application Due Date], >=DATE(2023, 7, 1), [Application Due Date]:[Application Due Date], <=DATE(2024, 6, 30))

owenhpdx

Thank you for your suggestion! Unfortunately the result of that formula corresponded to the number of rows whose dates fall between 7/1/23 and 6/30/24 (57). Just one of the rows that meets this date criteria has a Total Award Requested of over $1.8 million, so the result I'm getting ($371,181) doesn't make any sense.

Carson Penticuff

I suppose some of the entries in your Total Award Requested column could be formatted as test instead of number. Checking that would at least rule out one more variable.

owenhpdx

I wondered about that so checked the column format. The column is formatted Text/Number. Is there a way to format it as number only? I've applied U.S. Currency Format to the column, and all cells are formatted to U.S. Currency (e.g., $250,000.00). I really appreciate you trying to troubleshoot this with me.

Carson Penticuff

Unfortunately, no, Text/Number is your only option. Smartsheet attempts to distinguish between the two based automatically. Are you able to add a new column to this sheet temporarily? If so, you can add a checkbox and use this formula to distinguish any cells that are not being treated as numbers.

=IF(ISNUMBER([Total Award Requested]@row ), 1, 0)

owenhpdx

I wondered how I could put in a check like that! Thanks! Your suspicions were correct. Most of the numbers—despite being formatted as currency—are being read as text. The new column formula came up with mostly 0s—just a few scattered 1s here and there. Is there something I can do to format the data in all the award amount related columns as numbers?

Carson Penticuff

How is the currency column populated? Manual entry, or some type of linking or formula?

Jgorsich

Another column that just as the value function wrapped around your grant value "=value([Total Award Requested]@row)" and then point your AI formula at that may also highlight the issue.

Additionally, I've found issues similar to this with negative values - so watch out for that. You could modify my suggested formula with "=abs(value([Total Award Requested]@row))" just to get around it.

owenhpdx

Most of the data in the Smartsheet was imported from a spreadsheet, though I've manually entered some of the data for the most recent grants. It does look like the issue was in the import, as the most recent applications — likely entered directly into the Smartsheet—are reading as numbers.

Carson Penticuff

Depending on the number of rows you are dealing with, you may just go through the column and retype of all of the imported entries. This will typically cause them to be parsed as numbers.

owenhpdx

There are over 400 rows in the spreadsheet, and 6 of the columns list dollar amounts. Is there any other solution? I'd be willing to create additional columns and enter a function that results in pulling in the text from the existing column and turning it into a number. Surely Smartsheet anticipated the import of spreadsheets with numbers when creating the import function? :-(

Carson Penticuff

You can do something similar to what @Jgorsich mentioned above. Create a new column and set it equal to =VALUE([Total Award Requested]@row)

Jgorsich

https://community.smartsheet.com/discussion/comment/434292#Comment_434292

Note that you don't have to LEAVE that column in there if the problem is caused by importing- it will make Smartsheet treat everything as numbers, then you can copy it and use pastespecial to paste it as values back over your original entries. This would be a one time fix and won't prevent the issue happening again - but if you are sure it was a one time issue and won't happen in the future once you've fixed it, it will leave your sheet a little cleaner afterwards.

owenhpdx

Got it! That formula didn't work, but I suspected that it might be due to the currency formatting of the column. I was unable to select the column and change to "No Currency", but found another community discussion that had a formula for removing the "$". I used that and was able to pull out numbers. I used @Jgorsich's "Paste Special" and pasted values back into the Total Award Amount row, and now the report came back with what looks like the correct amount (over $19 million). I've been stuck on this for weeks. Thank you both for helping me to figure out what was wrong and how to fix it!

owenhpdx

This is the formula, by the way:

=VALUE(SUBSTITUTE([Total Award Requested]@row, "$", ""))