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
[TABLE="class: grid, width: 1000, align: left"]
<tbody>[TR]
[TD]Staff ID
[/TD]
[TD]Name
[/TD]
[TD]1st Development Need
[/TD]
[TD]2nd Development Need
[/TD]
[TD]3rd Development Need
[/TD]
[TD]4th Development Need
[/TD]
[TD]5th Development Need
[/TD]
[/TR]
[TR]
[TD]41726
[/TD]
[TD]Aaron
[/TD]
[TD]Personal Effectiveness
[/TD]
[TD]Business Management
[/TD]
[TD]Customer Service
[/TD]
[TD]Business Management
[/TD]
[TD]Customer Service
[/TD]
[/TR]
[TR]
[TD]101651
[/TD]
[TD]Aashna
[/TD]
[TD]Talent Management
[/TD]
[TD]Business Management
[/TD]
[TD]Customer Service
[/TD]
[TD]Health & Safety
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]27956
[/TD]
[TD]Abhishek
[/TD]
[TD]Performance Management
[/TD]
[TD]Audits
[/TD]
[TD]Leadership
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]66341
[/TD]
[TD]Aby
[/TD]
[TD]Customer Service
[/TD]
[TD]Talent Management
[/TD]
[TD]Performance Management
[/TD]
[TD]Recruitment
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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.