Using Multiple COUNTIFs

urban_player

New Member
Joined
Feb 22, 2019
Messages
12
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 OpenVery LowLowModerateHighVery High
0-7Mar - 19
8 - 15April - 19
16 - 24May - 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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,523
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Feb 22, 2019
Messages
12
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,523
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Glad to help. :biggrin:
 

Watch MrExcel Video

Forum statistics

Threads
1,109,492
Messages
5,529,173
Members
409,854
Latest member
rickcoba
Top