Using Multiple COUNTIFs

[urban_player]

Hi,

Thanks in advance for reading.

I am trying to crack a formula to look up multiple criteria and return me the total count value.

Brief description:

Creating a report for incidents, to show me, the number of incidents per month, which have been open for longer than 7 days and the different types of severity levels they fall under.

Please see a snip below: (cant upload image!)

Incident By Severity & Days Open	Very Low	Low	Moderate	High	Very High
0-7						Mar - 19
8 - 15						April - 19
16 - 24						May - 19
25+						June - 19

<tbody>
</tbody>

I have tried the following:

=COUNTIFS('Incident Summary (test)'!$C$6:$C$55,">="&$C$8,'Incident Summary (test)'!$C$6:$C$55,"<="&$D$8,'Incident Summary (test)'!$Y$6:$Y$55,$B$87:$B$91,'Incident Summary (test)'!$AI$6:$AI$55,">=0",'Incident Summary (test)'!$AI$6:$AI$55,"<=7")

Cell C8 & D8 is a cell containing month value as Feb-19
B$87:$B$91 is an array of:
Very Low
Low
Moderate
High
Very High

AI$6:$AI$55 is a column showing the number of days the incident has been open.

The formula only returns me with 0's.

I tried using

=COUNTIFS('Incident Summary (test)'!$C$6:$C$55,">="&$C$8,'Incident Summary (test)'!$C$6:$C$55,"<="&$D$8,'Incident Summary (test)'!$Y$6:$Y$55,"Very Low",'Incident Summary (test)'!AI6:AI21,"<7")

using a seperate formula for each cell, with matching text for the severity.

Right now, i am all out of ideas and hoping for some support.

Cheers

urban_player

 

[RoryA]

Welcome to the forum.

If you use multiple cells for a criterion, you'll get an array of results, which you need to SUM:

=SUM(COUNTIFS('Incident Summary (test)'!$C$6:$C$55,">="&$C$8,'Incident Summary (test)'!$C$6:$C$55,"<="&$D$8,'Incident Summary (test)'!$Y$6:$Y$55,$B$87:$B$91,'Incident Summary (test)'!$AI$6:$AI$55,">=0",'Incident Summary (test)'!$AI$6:$AI$55,"<=7"))

and that needs to be array-entered with Ctrl+Shift+Enter, not just Enter.

 

[urban_player]

Thank you, glad to be a member!

Can't thank you enough for the prompt response and solution.

It's working perfectly.

Have a great week sir

urban_player

 

[RoryA]

Glad to help.