Countif multiple range

SAkram

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

Nick Korna

@SAkram,

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! 🙂

Carson Penticuff

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

SAkram

this gives me the error message invalid Data type

Nick Korna

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

SAkram

Itis set to Date as type

Nick Korna

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.

SAkram

This is working thanks alot

SAkram

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

Nick Korna

It should be along the lines of:

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

Hope this helps!

SAkram

Thanks@Nick Korna

SAkram

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

Nick Korna

@SAkram,

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! 🙂

SAkram

Thanks@Nick Korna