Using an array formula in countifs range
<p>Afternoon guys,</p>

<p>I'm trying to evaluate a countifs formula using an array as the criteria range.</p>

<p>My table is named tblSkillsMatrix and is set up with the following headers;
Role    Staff   Skill 1 Skill 2 Skill 3 Skill 4 Skill 5 Skill 6 Skill 7 Skill 8 Skill 9 Skill 10 etc</p>

<p>There is a corresponding table called tblRoles.  The headers are Role titles and the rows hold the required skill e.g. Skill 1, Skill3, Skill 5, Skill 8 etc</p>

<p>I want my countifs function to count the number of items that show as trained under the relevant skill for the corresponding Role.  The normal formula looks something like this;</p>

<pre><code>=COUNTIFS(tblSkillsMatrix[[#All],[Role]],'Skills Summary'!C16,tblSkillsMatrix[[#All],[Skill 1]],"Trained",tblSkillsMatrix[[#All],[Skill 3]],"Trained",tblSkillsMatrix[[#All],[Skill 5]],"Trained",tblSkillsMatrix[[#All],[Skill 8]],"Trained")
</code></pre>

<p>Is there anyway to use an array formula to work out which columns I should use?  So the formula would look like this instead;</p>

<pre><code>=COUNTIFS(tblSkillsMatrix[[#All],[Role]],'Skills Summary'!C16,Array{columns to evaluate},"Trained")
</code></pre>

<p>Many thanks</p>