# Return Current Month in Cross Sheet Formula

Options
✭✭✭
edited 03/19/24

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!

Tags:

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭
Options

Thank you Aravind- that worked!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!