# Countifs plus a date range?

Options
✭✭✭✭
edited 01/07/22

Hello,

My sheet has a column with due dates across the year. I would like to count how many are due each month for a forecast report to show # due in Jan, # due in Feb, etc.

How do I write the formula for the date so that it includes all days of each month?

is unparseable.

Thank you for your time and help.

Carroll

• ✭✭✭✭✭✭
Options

The IFERROR function when used with date cells helps ignore blanks. (This is a way oversimplified explanation of it, but it'll do for a Monday morning.) For example, IFERROR(MONTH(@cell),0)=1 means see if the month in the cell is January, but if there's an error (which happens with cells that are not dates - no month to look for), don't count it.

Let's try this:

=COUNTIFS([Adjusted due date]:[Adjusted due date], IFERROR(MONTH(@cell), 0) = 1, [Adjusted due date]:[Adjusted due date], IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), [Other column]:[Other column], NOT(CONTAINS("ABCD EFG", @cell)))

You'll, of course, want to substitute the "Other column" text with the actual name of said other column, and the "ABCD EFG" with the actual text you do not want it to contain.

Let me know if it works!

• ✭✭✭✭✭✭
Options

Try this:

=COUNTIF([Adjusted Due Date]:[Adjusted Due Date], IFERROR(MONTH(@cell), 0) = 1)

That's for January; then for the rest, you'd adjust the MONTH(@cell)= number to 2 for Feb, 3 for March, etc.

Hope this helps! Let me know if it works for you.

Best,

Heather

• ✭✭✭✭
Options

Thanks @Heather Duff !

Yes that works! It is exactly what I asked for.

But now I realize I have dates in 2023 that I need to exclude for this year's forecast. So how do I nest the year exclusion?

=COUNTIF([Adjusted Due Date]:[Adjusted Due Date], IFERROR(MONTH(@cell), 0,(YEAR@cell),0) = 1)

Did not parse sadly. I am afraid the If Error function is not familiar to me.

Also I need to exclude certain rows that contain the words "ABCD EFG" in another column.

Is it possible to next that exclusion as well?

Thank you some more!

Carroll

• ✭✭✭✭✭✭
Options

The IFERROR function when used with date cells helps ignore blanks. (This is a way oversimplified explanation of it, but it'll do for a Monday morning.) For example, IFERROR(MONTH(@cell),0)=1 means see if the month in the cell is January, but if there's an error (which happens with cells that are not dates - no month to look for), don't count it.

Let's try this:

=COUNTIFS([Adjusted due date]:[Adjusted due date], IFERROR(MONTH(@cell), 0) = 1, [Adjusted due date]:[Adjusted due date], IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), [Other column]:[Other column], NOT(CONTAINS("ABCD EFG", @cell)))

You'll, of course, want to substitute the "Other column" text with the actual name of said other column, and the "ABCD EFG" with the actual text you do not want it to contain.

Let me know if it works!

• ✭✭✭✭
Options

Thanks for the Monday Morning help!

I got the year to work but not the exclusion from the other column. I will fiddle with it now that I have a better understanding. I appreciate your help.

• ✭✭✭✭✭✭
Options

@Carroll Wall Happy to help! Let me know if you want to work through the exclusion.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!