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

[TABLE="width: 500"]
<tbody>[TR]
[TD]Incident By Severity & Days Open[/TD]
[TD]Very Low[/TD]
[TD]Low[/TD]
[TD]Moderate[/TD]
[TD]High[/TD]
[TD]Very High[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0-7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mar - 19[/TD]
[/TR]
[TR]
[TD]8 - 15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]April - 19[/TD]
[/TR]
[TR]
[TD]16 - 24[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]May - 19 [/TD]
[/TR]
[TR]
[TD]25+[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]June - 19[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.