# COUNTIFS using different sheet for range and month for criteria

Options

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**

Tags:

• ✭✭✭✭✭✭
Options

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")

• ✭✭✭✭✭✭
Options

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")

• Options

Thank you so much @Paul Newcome that worked!

• Options

@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?

• ✭✭✭✭✭✭
Options

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.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!