0

Thanks for your help in advance!

So my problem is the following; I have a sheet which I use to log risks that come up in my project. They are graded from Critical to Low, and are either "Open" or "Closed". I want a formula that will count only the open actions which are critical. So far i've got =COUNTIF(Risks!E:E,"Critical"), which correctly counts the number of critical risks, but obviously doesn't take into account if they are open or closed. I tried using än IF statement at the start, but found that =IF(Risks!H:H="Open",COUNTIF(Risks!E:E,"Critical")) wasn't really returning what I needed.

I definitely remember getting this to work before, but I'm thinking perhaps I used VBA? Been a while since I've used excel!

Any help much appreciated!

Ed

JavaStarta
  • 67
  • 1
  • 11

1 Answers1

2

Use COUNTIFS():

=COUNTIFS(Risks!E:E,"Critical",Risks!H:H,"Open")

COUNTIFS() allows the use of multiple criteria.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • I ran that one through my sheet, and it came up with a #NAME? Error. A little investigation and I realised that because I'm forced to run the 2003 version of Excel...that function doesn't work. However thanks for the suggestion, I will try it with a newer version and see if I can get it to work there! Is it possible to replicate this function with perhaps another, 2003 friendly, formula? Cheers! – JavaStarta Apr 19 '16 at 09:42
  • @JavaStarta Use `=SUMPRODUCT((Risks!$E$1:$E$100="Critical")*(Risks!$H$1:$H$100="Open"))` avoid full column references it will slow down the computation. – Scott Craner Apr 19 '16 at 15:53