Using an array formula in countifs range

Question

Afternoon guys,

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

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

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

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;

=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")

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

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

Many thanks

Of what should the part {columns to evaluate} part of Array{columns to evaluate} consist? The full table column references (tblSkillsMatrix[[#All],[Skill 1], etc.)? If so, this won't really make the formula any more concise. Perhaps you wish to reference the desired columns via their relative position within the table (e.g. {1,4,6})? — XOR LX
– XOR LX, Commented Oct 25, 2016 at 15:31
Hi, thanks for helping. In tblSkillsMatrix, the column headers are Skill 1, Skill 2 etc. In tblRoles, the column headers are the names of job types and the rows show which Skills they should have e.g. Skill 1, Skill 5 etc. Using an array formula {=INDIRECT("tblRoles[JobType]")} returns all the skills listed under column heading "JobType". So this may be Skill 3, Skill 8 etc. I need to be able to tell the countifs formula to use those columns in tblSkillsMatrix. Hope this make sense? — PeteBradshaw
– PeteBradshaw, Commented Oct 25, 2016 at 16:20

XOR LX · Accepted Answer · 2016-10-25 19:46:03Z

1

Array formula**:

=SUM(IF(tblSkillsMatrix[Role]='Skills Summary'!C16,IF(MMULT(0+(T(OFFSET(INDIRECT("tblSkillsMatrix["&TRANSPOSE(tblRoles[JobType])&"]"),ROW(tblSkillsMatrix[Role])-MIN(ROW(tblSkillsMatrix[Role])),))="Trained"),ROW(tblRoles[JobType])^0)=COUNTA(tblRoles[JobType]),1)))

Note that I changed your table references to exclude the headers, since I presume that you don't actually need to include them in the count?

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

edited Oct 25, 2016 at 19:46

answered Oct 25, 2016 at 17:08

XOR LX

7,7821 gold badge19 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

PeteBradshaw Over a year ago

That's brilliant, thanks, but I don't think I've asked quite the right question!!! My bad... What I need is to count the rows where the JobType & ALL Skills on that row for a team member are shown as trained. Sorry, its been a really long day!

PeteBradshaw Over a year ago

ok - I've got this to work so far {=SUM((tblSkillsMatrix[Role]="Civil")*(tblSkillsMatrix[Skill 3]="Trained")*(tblSkillsMatrix[Skill 1]="Trained")*(tblSkillsMatrix[Skill 5]="Trained")*(tblSkillsMatrix[Skill 8]="Trained"))} But how can I change it to something like this {=SUM((tblSkillsMatrix[Role]="Civil")*INDIRECT("tblSkillsMatrix["&tblRoles[Civil]&"]"="Trained"))}?

XOR LX Over a year ago

Ah, ok. I'm afraid that that makes the required solution quite complex. Have amended the post.

PeteBradshaw Over a year ago

I really appreciate your help with this, I'm fairly new to array formulas, I'd normally be doing this in vb! Did you say you amended the post? I can't see it...

XOR LX Over a year ago

Well - the new updated formula is about three times longer, so I'm surprised to hear you say that you can't see it!

|

Collectives™ on Stack Overflow

Using an array formula in countifs range

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related