Formula Help

DLABS Consulting

Hello,

Looking for a little quick assistance. Not sure why I can figure this out, but I guess I'm just not as intuitive with the SmartSheet logic.

I have a metrics sheet that I am using to provide data from other sheets to populate a dashboard. I currently have this formula and similar formulas that pull data within a given year:

=COUNTIF({Created Date Range}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

This issue with this is that once the new year comes, all of my data will go away. How do I make this pull information from "2018?" I assumed it would be ...YEAR(2018), but that's not the case. Any help is appreciated.

Mike Wilday

Try one of these. 

=COUNTIF({Created Date Range}, IFERROR(YEAR(@cell), 0) = 2018)

=COUNTIF({Created Date Range}, IFERROR(YEAR(@cell), 0) = "2018")

If they don't work let me know what error you're getting. 

DLABS Consulting

That seemed to work, thanks!

How about if I wanted to also parse out date by the month within a particular year?

For instance, I am using this formula to calculate and report a sales commission based on a sales tracking sheet. This current formula calculates the commission based on a particular month, but does not separate by year.

=SUM(0.05 * (SUMIF({Consulting Time Tracker/Billing Range 1}, IFERROR(MONTH(@cell), 0) = 12, {Consulting Time Tracker/Billing Range 2})))

Mike Wilday

Not sure if you figured this out or not but you can use SUMIFS to provide multiple criterion. You can repeat the same range and check the month as well. 

https://help.smartsheet.com/function/sumifs