COUNTIFS using different sheet for range and month for criteria

lexiheftler

I am attempting to get this formula in my tracker sheet to work to count all the instances in the reference sheet where a certain month in the date column occurs and the answer to a question in another column is no. This is the formula I have come up with and I know it's wrong but all of the fixes I have attempted have not worked. Currently it returns a 0 but I know that is incorrect.

=COUNTIFS({Wave 2 Wrap Up Date}, =12, {Wave 2 Wrap Up Close}, "No")

**Note: The ranges are from the same sheet, just different reference names**

Thanks in advance!

Paul Newcome

To get a formula to only look at the month within a date type column you would need somethign like this:

=COUNTIFS({Wave 2 Wrap Up Date}, IFERROR(MONTH(@cell), 0)=12, {Wave 2 Wrap Up Close}, "No")

Paul Newcome

To get a formula to only look at the month within a date type column you would need somethign like this:

=COUNTIFS({Wave 2 Wrap Up Date}, IFERROR(MONTH(@cell), 0)=12, {Wave 2 Wrap Up Close}, "No")

lexiheftler

Thank you so much @Paul Newcome that worked!

lexiheftler

@Paul Newcome would I just repeat the formula in the same cell if I wanted it to count instances for Q1 for instance, month 1, 2 and 3?

Paul Newcome

If you wanted to count for within a specific quarter, there are a number of ways to do this. The most flexible (in case you need to do a fiscal quarter that doesn't line up with a calendar quarter) would be:

=COUNTIFS({Wave 2 Wrap Up Date}, OR(IFERROR(MONTH(@cell), 0) = 1, IFERROR(MONTH(@cell), 0) = 2, IFERROR(MONTH(@cell), 0)=3), {Wave 2 Wrap Up Close}, "No")

You would basically wrap all three criteria for the same range in an OR statement and then repeat the IFERROR/MONTH = and enter the month numbers you want.

Keep in mind though that there are a number of variables that can come into play (years for instance), and depending on exact needs there could be more efficient ways of writing it. That's just the easiest modification to the existing formula.