Excel Formula for CountIFs with multiple cell referenced Named Range

0
votes

I am trying to count all the instances within a table (table name = "data") using the CountIFs formula:

=COUNTIFS(data[[Status]:[Status]],"="&INDEX(Actionable,1), data[[Age (Days)]:[Age (Days)]],">"&"14", data[[Support Owner]:[Support Owner]],"="&$B38)

The formula works right now because I am only referencing the value in the first row of the Named Range "Actionable" by using the INDEX function.

What I want to do is be able to have the formula look at all the values in the named range and find all the instances in the column data[[Status]:[Status]].

Is this possible?

1
excelexcel-formula

1 Answers

1
votes

If you use the named range alone as the criterion then the COUNTIFS formula will return an array (one value for each value in Actionable) so you can then wrap the whole thing in a SUMPRODUCT function to get the sum of that array without "array entry". That sum represents your total count, i.e.

=SUMPRODUCT(COUNTIFS(data[[Status]:[Status]],Actionable,data[[Age (Days)]:[Age (Days)]],">14", data[[Support Owner]:[Support Owner]],$B38))

Note - if Actionable contains duplicates then you will also count those values more than once