How to calculate a COUNTIF when using a date 01/01/2020 month to date

Chareese Hayes

Good Morning Everyone,

I am trying to create a formula that pulls from a master spreadsheet that calculates the number returned based on a date from the data sheet. I have columns that indicate month to date, September, August, July, June, May, April, March....

=COUNTIFS({LPSC tickets Range 1}, "Production", {LPSC tickets Range 2}, "S2 - Urgent", {LPSC tickets Range 3}, "AFR") (This formula works).

How get the number of tickets in production with severity 2, for client AFR, Month to Date and a separate formula for within September, August, July...Columns?

I was trying to use the formula below. Is it correct for this date format?

The date field on the data sheet is in the following format: 01/01/2020

=COUNTIF({LPSC tickets Range 4}MONTH, TODAY(), ={LPSC tickets Range 4})

Kelly Moore

Hi Chareese

@Mike Blumenstein posted an answer about a year ago that may be relevant to what you're asking to do. Not knowing exactly how your data is arranged on either of your two sheets, it's hard to know for sure. https://community.smartsheet.com/discussion/55771/invalid-data-type-adding-month-to-countifs-with-cross-sheet-refs

In the formula you attempted, one error is that you trying to compare the month of your cross referenced date to Today() - a full date. Therefore, in addition to correcting the @cell references, try MONTH(TODAY()) in your formula to compare month number to month number.

Because counting dates can be tricky in cross references, I typically use a helper column and convert my dates to Month numbers on my main sheet if I want to count by month, I do this frequently pulling into a summary or metric sheet by having a column for month Name, a column for the CountIfs result, and a column for the month number. The month number column is usually hidden but is the column I referenced as criteria in my countifs: @cell=[Month Number]@row

Give us a bit more info about both your sheets and the community can better assist.

Kelly

Genevieve P.

Hi @Chareese Hayes

Your second formula should work. The INVALID REF error (see here) has to do with your cross-sheet column references {in these}. I would suggest checking each of your Ranges to make sure they're looking at the correct associated column.

Click on each one and select Edit Reference from the little pop-up window. Then make sure that they each highlight the correct column. Keep in mind if you update the reference to look at a new column, any formulas that use a reference with that same title {in here} will automatically update to look at this new column as well. Our Help Center has more information on Cross Sheet References (see here).

Cheers!

Genevieve

Kelly Moore

Hi Chareese

@Mike Blumenstein posted an answer about a year ago that may be relevant to what you're asking to do. Not knowing exactly how your data is arranged on either of your two sheets, it's hard to know for sure. https://community.smartsheet.com/discussion/55771/invalid-data-type-adding-month-to-countifs-with-cross-sheet-refs

In the formula you attempted, one error is that you trying to compare the month of your cross referenced date to Today() - a full date. Therefore, in addition to correcting the @cell references, try MONTH(TODAY()) in your formula to compare month number to month number.

Because counting dates can be tricky in cross references, I typically use a helper column and convert my dates to Month numbers on my main sheet if I want to count by month, I do this frequently pulling into a summary or metric sheet by having a column for month Name, a column for the CountIfs result, and a column for the month number. The month number column is usually hidden but is the column I referenced as criteria in my countifs: @cell=[Month Number]@row

Give us a bit more info about both your sheets and the community can better assist.

Kelly

David Joyeuse

Hi @Chareese Hayes

I believe you're looking for this:

=COUNTIFS({LPSC tickets Range 1}, "Production", {LPSC tickets Range 2}, "S2 - Urgent", {LPSC tickets Range 3}, "AFR", {LPSC tickets Range 4}, IFERROR(MONTH(@cell),0)=9)

Just replace the number in the last argument according to the month you want to count.

Chareese Hayes

Hi David,

I used this in the MTD November column

=COUNTIFS({LPSC tickets Range 1}, "Production", {LPSC tickets Range 2}, "S2 - Urgent", {LPSC tickets Range 3}, "AFR", {LPSC tickets Range 4}, IFERROR(MONTH(@cell), 1) = 11)

Results = INVALID REF

Chareese Hayes

I also tried:

=COUNTIFS({LPSC tickets Range 1}, "Production", {LPSC tickets Range 2}, "S2 - Urgent", {LPSC tickets Range 3}, "AFR", {LPSC tickets Range 4}, IFERROR(MONTH(@cell), 0) = 11)

11= November

Genevieve P.

Hi @Chareese Hayes

Your second formula should work. The INVALID REF error (see here) has to do with your cross-sheet column references {in these}. I would suggest checking each of your Ranges to make sure they're looking at the correct associated column.

Click on each one and select Edit Reference from the little pop-up window. Then make sure that they each highlight the correct column. Keep in mind if you update the reference to look at a new column, any formulas that use a reference with that same title {in here} will automatically update to look at this new column as well. Our Help Center has more information on Cross Sheet References (see here).

Cheers!

Genevieve

Chareese Hayes

It worked. Thank you.

Chareese