Return Current Month in Cross Sheet Formula
Hi all,
I need to pull counts of events related to account #'s for the current month. On the raw data sheet, I have the row calculation working with this formula:
=COUNTIFS([Event Date]@row, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), [Event Date]@row, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))
However, that will just return a 0 or 1, as it is only looking at a single event at a time. I am trying to create a roll up for monthly data for the accounts, and I can't seem to get the cross sheet reference correct for the Month and Year.
Here is where I am stuck:
=COUNTIFS({Alarm#}, [Alarm #]@row, {Event Date}, IFERROR(MONTH({Event Date}), 0) = MONTH(TODAY()), {Event Date}, IFERROR(YEAR({Event Date}), 0) = YEAR(TODAY()))
This formula does not give me an error, only a 0. I know that there are events to be counted, so it should not be a 0. I think it may be the where I replaced the (MONTH(@cell) with (MONTH(Event Date)....but I am not sure what would replace the @cell on a cross sheet reference.
Appreciate any and all help!
Best Answer
-
Your sheet formula tags rows that fall in the current month. If your objective in sheet 2 is to pick the times the same Alarm # appears for the current month, your formula in sheet 2 will be = COUNTIFS({Alarm #}, [Alarm #]@row, {Helper column with 1 or 0}, 1)
Thanks,
Aravind GP| Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com
Answers
-
Your sheet formula tags rows that fall in the current month. If your objective in sheet 2 is to pick the times the same Alarm # appears for the current month, your formula in sheet 2 will be = COUNTIFS({Alarm #}, [Alarm #]@row, {Helper column with 1 or 0}, 1)
Thanks,
Aravind GP| Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com
-
Thank you Aravind- that worked!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!