Count Year and Month Only from Separate Smartsheet

svassair

Hi all!

New smartsheet user and learning formulas! I'm trying to count the number of jobs in a month per project type from a separate smartsheet (to eventually be turned into a graph). The date format in the original smartsheet or month/day/year. I just want to count the month + year. The Project Type is also in a separate column. So far, I can't even get the date/year count to work. I'm using the following formula for the count:

=COUNTIF({Request Tracker Range 1}; AND(IFERROR(MONTH(@cell); 0) = 1; IFERROR(YEAR(@cell); 0) = 2023))

Am I way off base?

James Keuning

Here is how I would do this.

(I am going to refer to your calculated results sheet as the "rollup" and the source sheet as "data.")

First, create a helper column in your data sheet that takes your date and converts it to MMYYYY. You can hide this later. Call this HELPER.

=VALUE(MONTH(DateField@row) + "" + YEAR(DateField@row))

(we put the VALUE at the beginning so we keep the data consistent, it's often not needed)

Then in your rollup sheet:

Create the same sort of formula so that you get your month and year in the same format. Again, you can hide this.

=VALUE(MONTH(DateField@row) + "" + YEAR(DateField@row))

Call this column MMYYYY

Then your formula in your rollup sheet is:

=COUNTIF({DATA.HELPER}, MMYYYY@row)

{DATA.HELPER} points to the HELPER column in your data sheet