Help with month end formula

Frances Lewis

Hi,

I'm building a dashboard to track referral responses. The missing part of my dashboard is I need create a formula that gives the total for each category by month and year.

I can't figure out what function I need to add the the formulas I already have to break down the total by month and year.

The formulas I've built so far:

Only counts the referral type "Emergency - 24 hours IHCA response needed

=COUNTIF({Referral Type}, ="Emergency - 24 hour IHCA response needed")

Counts the total number or referrals received:

=COUNT({Timestamp})

Gives a total of successful referrals:

=COUNTIF({E-mail Outcome}, "Successful")

Thank you

Frances

Genevieve P.

Hi @Frances Lewis

You're on the right track! The next step is to use a COUNTIFS (plural) function to look for multiple criteria. Then you can use the MONTH function and the YEAR function to search for certain dates.

With COUNTIFS, first you list the range (I'll call this your {Date column}), then you list your criteria afterwards. Since it's a cross-sheet formula, you'll want to have @cell as the "date" within the MONTH and YEAR functions, to specify it should look in the individual cells of the previously listed column.

Ex, this would look for January (or month 1) in the year 2020:

=COUNTIFS({Referral Type}, ="Emergency - 24 hour IHCA response needed", {Date column}, MONTH(@cell) = 1, {Date column}, YEAR(@cell) = 2020)

Now, you can sometimes receive errors with date functions when it's looking through entire columns like this and coming across blank cells. To prevent that, you can wrap an IFERROR function around both the MONTH and the YEAR functions.

Full Formula:

=COUNTIFS({Referral Type}, ="Emergency - 24 hour IHCA response needed", {Date column}, IFERROR(MONTH(@cell), 0) = 1, {Date column}, IFERROR(YEAR(@cell), 0) = 2020)

You can use that same statement in each of your formulas, just change out the criteria. So for February, have the MONTH bit search for 2 instead of 1:

IFERROR(MONTH(@cell), 0) = 2

For 2019, have the YEAR search for 2019:

IFERROR(YEAR(@cell), 0) = 2019

Let me know if this makes sense or if you have any questions!

Cheers,

Genevieve

Genevieve P.

Hi @Frances Lewis

The INVALID REFERENCE is referring to your cross-sheet references, the ones {in these}.

I had placed example text in my references to show what columns you should be selecting in the other sheet, but you will need to go through the "reference another sheet" process in order to link each of these with the correct column.

For example, the {Date column} reference would be the column in your source sheet that has the dates you're searching through. Copy/Paste the formula, then delete out the reference in the formula and select "reference another sheet":

Then when you find your source sheet, select the date column and rename the reference something easily identifiable... perhaps even "Date column" so you don't need to do the same thing for the second reference.

Does that make sense? You can learn more about cross sheet references here.

Cheers!

Genevieve

Genevieve P.

Hi @Frances Lewis

You're on the right track! The next step is to use a COUNTIFS (plural) function to look for multiple criteria. Then you can use the MONTH function and the YEAR function to search for certain dates.

With COUNTIFS, first you list the range (I'll call this your {Date column}), then you list your criteria afterwards. Since it's a cross-sheet formula, you'll want to have @cell as the "date" within the MONTH and YEAR functions, to specify it should look in the individual cells of the previously listed column.

Ex, this would look for January (or month 1) in the year 2020:

=COUNTIFS({Referral Type}, ="Emergency - 24 hour IHCA response needed", {Date column}, MONTH(@cell) = 1, {Date column}, YEAR(@cell) = 2020)

Now, you can sometimes receive errors with date functions when it's looking through entire columns like this and coming across blank cells. To prevent that, you can wrap an IFERROR function around both the MONTH and the YEAR functions.

Full Formula:

=COUNTIFS({Referral Type}, ="Emergency - 24 hour IHCA response needed", {Date column}, IFERROR(MONTH(@cell), 0) = 1, {Date column}, IFERROR(YEAR(@cell), 0) = 2020)

You can use that same statement in each of your formulas, just change out the criteria. So for February, have the MONTH bit search for 2 instead of 1:

IFERROR(MONTH(@cell), 0) = 2

For 2019, have the YEAR search for 2019:

IFERROR(YEAR(@cell), 0) = 2019

Let me know if this makes sense or if you have any questions!

Cheers,

Genevieve

Frances Lewis

Hi @Genevieve P

Thanks for your help. When I input the formula it returns #INVALID REF error. I'm not sure what I am missing for this formula. I think it has to do with the parentheses. I tired to let Smartsheet auto input the parentheses, I still get the same error.

--

Frances

Genevieve P.

Hi @Frances Lewis

The INVALID REFERENCE is referring to your cross-sheet references, the ones {in these}.

I had placed example text in my references to show what columns you should be selecting in the other sheet, but you will need to go through the "reference another sheet" process in order to link each of these with the correct column.

For example, the {Date column} reference would be the column in your source sheet that has the dates you're searching through. Copy/Paste the formula, then delete out the reference in the formula and select "reference another sheet":

Then when you find your source sheet, select the date column and rename the reference something easily identifiable... perhaps even "Date column" so you don't need to do the same thing for the second reference.

Does that make sense? You can learn more about cross sheet references here.

Cheers!

Genevieve