Return Current Month in Cross Sheet Formula

Kim Stephens

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!

AravindGP

Hi @Kim Stephens

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)

AravindGP

Hi @Kim Stephens

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)

Kim Stephens

Thank you Aravind- that worked!