# Using Multiple COUNTIFs

#### urban_player

##### New Member
Hi,

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.

 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

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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.

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

Replies
1
Views
64
Replies
18
Views
219
Replies
1
Views
114
Replies
5
Views
352
Replies
6
Views
1K

1,203,605
Messages
6,056,262
Members
444,853
Latest member
sam69

### 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.

### Which adblocker are you using?

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

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