Using Multiple COUNTIFs

urban_player

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

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,820
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
14
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
38,820
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Glad to help. :biggrin:
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,628
Messages
5,838,447
Members
430,549
Latest member
jayjay2022

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top