Counting how many times text occurs in the 5 columns next to staff ID

[Jess_H]

Hi guys,

I am trying to count how many times a text appears next to a staff ID across 5 columns. I foolishly thought a countifs formula would sort it, but I'm getting an error so that's obviously not right

Below is an example of the data I'm working from (I can't attach a workbook...)

What I want is column headers Staff ID, Name, Personal Effectiveness, Business Management, Customer Service, Leadership, Audits, Health & Safety, Talent Management, Performance Management, Recruitment, then a count of how many time that has appeared in the 5 development needs columns - i.e. Aaron would have a 1 under Personal Effectiveness, a 2 under Business Management, and a 2 under Customer Service

Original Data

Staff ID	Name	1st Development Need	2nd Development Need	3rd Development Need	4th Development Need	5th Development Need
41726	Aaron	Personal Effectiveness	Business Management	Customer Service	Business Management	Customer Service
101651	Aashna	Talent Management	Business Management	Customer Service	Health & Safety
27956	Abhishek	Performance Management	Audits	Leadership
66341	Aby	Customer Service	Talent Management	Performance Management	Recruitment

<tbody>
</tbody>

 

[PaddyD]

"...countifs formula would sort it, but I'm getting an error so that's obviously not right "

Don't see why it shouldn't. Why not post up the formula you tried.

Assuming that you could confirm that the needs were always typed out the same (perhaps, e.g. because you had them populated from drop down boxes, for example), then few extra columns with the need text in the heading and countifs() should do exactly what you're after.

 

[Jess_H]

Hi,

Thanks for your response, the formula I'm using is

=COUNTIFS('[Development Needs Raw Data.xlsx]Sheet1'!$A:$A,$A2,'[Development Needs Raw Data.xlsx]Sheet1'!$C:$G,C$1)

I'm just getting #VALUE ! when I do this. I had originally collected the data using dropdown (so all text is identical), but I have pasted values into a brand new workbook and been up to Data Validation and clicked Clear All to double check that the list option is gone.

Any ideas? I'm using Excel 2013 if that changes anything

Thanks!

 

[Aladin Akyurek]

COUNTIFS requires equally shaped ranges. You have A:A and C:G which do not agree on that score.

Try rather: Control+shift+enter, not just enter...

=SUM(IF('[Development Needs Raw Data.xlsx]Sheet1'!$A:$A=$A2,IF('[Development Needs Raw Data.xlsx]Sheet1'!$C:$G=C$1,1)))

Two points to note about this formula:

a. Better not to refer to whole columns for reasons of efficiency.
b. It does count every A2 and C1 occurrence. That is, for example a record of Sheet1!A1=A2, Sheet1!C1 = C1, and Sheet1!H1 = C1, will yield a count of 2, not 1.