Countifs plus a date range?

Carroll Wall

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?

=Countifs [Adjusted Due date]:[Adjusted Due Date], DATE>=Jan1,[Adjusted Due date]:[Adjusted Due Date], DATE<=Jan31

is unparseable.

Thank you for your time and help.

Carroll

Heather Duff

Hi @Carroll Wall ,

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!

Heather Duff

Hi @Carroll Wall ,

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

Carroll Wall

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

Heather Duff

Hi @Carroll Wall ,

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!

Carroll Wall

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.

Heather Duff

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