Formula Help

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.

Find more posts tagged with

Manufacturing

Formulas

Project Management

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

A multiple SUMIF function
My current formula is below. I would like to update this so that I only get a total of those that have been Quoted and have a timeframe data of 2025. How do I add this extra filter into the formula? =SUMIF(Status:Status, "Quoted", [Est. GP]:[Est. GP])
Missing column in Report if I use a formula
I'm not able to "view by" a field that I have in my sheets - Quarters (Q1, Q2, Q3, Q4) - in my report. I think it is because I have used a formula to calculate the quarters in my sheets. Does anyone know a way to get a formula based field on the dropdown for view by in my report?
SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…