COUNTIF formula for a date range

Katelyn Buress

Hi, I am brand new to SmartSheets and trying to pull some high level metrics from a sheet into the Summary section. I have never used complex formulas before in Excel or SmartSheets and am trying to find the most basic answer around how to build a COUNTIF formula to show how monthly numbers for my team. My sheet has 3 different date columns and I want a count based on one of the columns, for everything that happens within a month. I know that everything I want to do is possible with SmartSheet, but am struggling to put it into action.

I've been watching videos on YouTube, LinkedIn Learning, and searching the community but I am looking for the most basic How To of writing a formula in SmartSheet using COUNTIFs. If I get this figured out, I'll try for building dashboards with the metrics, but am definitely not that advanced yet :)

Heather Duff

Hi Katelyn,

Are you able to share a screenshot of your sheet, with any sensitive information blocked? Also, please describe a bit more of what you're looking for - do you want a rolling total for items in the current month, the past 30 days, or do you want totals for each month? Having this extra information will help figure out what formula you need.

Best,

Heather

Katelyn Buress

Hi Heather, in my Smartsheet, I have a column that includes a check box to mark is something is tracking towards my team's EOY metrics. I also have a column with that includes the date something occurred. I am trying to create a Summary on the Smartsheet that shows the Count of line items that include a check box and have occurred within the specific calendar month.

I feel like this should be a pretty simple formula, but I just do not know the in's and out's of how to even build a Smartsheet formula since I am new at this.

Heather Duff

Katelyn,

Try something like this, where Date is the name of the column with dates in it (make sure it's set as a date column in the column properties), Check is the checkbox column, and Count is the column with the count of items you want to sum.

I also added two helper columns where I am summing my data, followed by a final Sum column. See screenshot. The numbers in the month column represent the months of the year (Jan=1, Dec=12).

Here's the formula I used in the Sum column:

=SUMIFS(Count:Count, Check:Check, 1, Date:Date, MONTH(@cell) = Month@row, Date:Date, YEAR(@cell) = Year@row)

If you plan on using the Sheet Summary feature, you can use that same formula in it, but instead of =Month@row and =Year@row, you'll have to manually enter the month/year (=1 for January in the month formula, =2021 for 2021 in the year formula, etc.) for each month you want to sum.

You can also sum the data on a separate sheet, which is actually my preferred method of summarizing data. You can do it by creating a new sheet, starting to type the formula =SUMIFS( ...and then you'll need to click the "reference another sheet" link. From there, you'll search for your source sheet, name the range (column) you want to reference, and continue with the formula. Here's what a formula with a cross-sheet reference will look like:

=SUMIFS({Count}, {Check}, 1, {Date}, MONTH(@cell) = Month@row, {Date}, YEAR(@cell) = Year@row)

You would still use the month/year helper columns.

Hope this helps! Happy Smartsheet-ing!

Best,

Heather

Katelyn Buress

Heather, I just wanted to say that I appreciate your help so much and that you took the time to put this together for me. I am feeling as if I'm a lost cause over here because now I am searching for "What are helper columns?" and am still struggling with what to put where with what I am trying to accomplish. Thank you so much - I appreciate your time.

Heather Duff

Katelyn,

Don't be discouraged - it's a learning process, for sure, and you're brand new! I also could have elaborated on the helper column thing. Helper column are really just regular old columns (whether text, check boxes, or any other column type) that "help" your formula. In this case, they're just helping the formula tell you what month and year you're looking to total. Does that make sense?

Keep at it. Keep asking questions, trying things, and asking more questions. I have learned a lot in working in Smartsheet for just over a year by trial and error, YouTube, Google, and this community. If you have access to the Center of Excellence or learn.smartsheet.com, those are great places to start for tutorials.

Ask more questions until we get this working how you need! We're all in this together. :)

Best,

Heather