Countif multiple range

✭✭✭

I want to calculate Ticket Priority with a specific date; how to use the counif formula like

How many tickets which Have Priority Normal and received in the month of June Only

• ✭✭✭✭✭✭

Like this:

=COUNTIF(Date:Date, AND(@cell >= DATE(2023, 6, 10), @cell <= DATE(2023, 6, 24)))

If you have additional criteria (such as in the first example, you would switch to COUNTIFS:

=COUNTIFS(Date:Date, AND(@cell >= DATE(2023, 6, 10), @cell <= DATE(2023, 6, 24)), Type:Type, "Normal")

If you need to change the range then just alter the numbers in the DATEs (Year, Month, Day).

Hope this helps! 🙂

• ✭✭✭✭✭✭

=COUNTIFS([Date]:[Date], MONTH(@cell) = 6, [Priority Level]:[Priority Level], "Normal")

• ✭✭✭

this gives me the error message invalid Data type

• ✭✭✭✭✭✭

Is your Date column set to Date as type? If not, the MONTH portion of the formula won't work properly.

• ✭✭✭

Itis set to Date as type

• ✭✭✭✭✭✭

In which case, amend the formula above slightly:

=COUNTIFS(Date:Date, IFERROR(MONTH(@cell), 0) = 6, [Priority Level]:[Priority Level], "Normal")

This should give the desired result.

• ✭✭✭

This is working thanks alot

• ✭✭✭

Can you help me same with the average Formula? with month

• ✭✭✭✭✭✭

It should be along the lines of:

=AVERAGEIF(Date:Date, IFERROR(MONTH(@cell), 0) = 6, [Resolution time (min)]:[Resolution time (min)])

Hope this helps!

• ✭✭✭

Thanks@Nick Korna

• ✭✭✭

How to use this formula for specific date Range like 6/10/2023 till 6/24/2023

• ✭✭✭✭✭✭

Like this:

=COUNTIF(Date:Date, AND(@cell >= DATE(2023, 6, 10), @cell <= DATE(2023, 6, 24)))

If you have additional criteria (such as in the first example, you would switch to COUNTIFS:

=COUNTIFS(Date:Date, AND(@cell >= DATE(2023, 6, 10), @cell <= DATE(2023, 6, 24)), Type:Type, "Normal")

If you need to change the range then just alter the numbers in the DATEs (Year, Month, Day).

Hope this helps! 🙂

• ✭✭✭

Thanks@Nick Korna

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!