Struggling with CountIf function

[Grimlocc]

Hello,
I am struggling with COUNTIF and COUNTIFS formulas so I’m hoping I may be able to get some help here?

I have two columns of data; column A contains a list of locations and column B contains either Yes, No or is blank.

There are 33 unique locations and each location appears multiple times down column A.

In Column B, the entries Yes or No tell me whether a person at a particular location has a certain skill. There are also blank entries where I don’t know whether a person has a skill or not.

I have a separate workbook listing all of the locations across the top and under each location, I want a count of everybody who has the particular skill (From Column B on the other workbook) I am looking at. It looks like this:

Skill | Location 1 | Location 2 | Location 3 | Location 4 | …

Skill1
Skill2
Skill 3
Skill 4
…

Assume I am putting my formula in B2 (this would be below Location 1 and to the right of Skill 1).

What I am trying to do is tell the formula to look in the workbook with the locations and skills, find every instance of Location 1 in column A and for each instance of Location 1, look across the row to see whether the Skill 1 column has a Yes in it. Then I want to count up all of the instances of Yes to give me a number. This number should indicate how many people in Location 1 has Skill 1.

So far I have done this:

=COUNTIFS('[Locations and Skills.xlsm]Staff Area'!$G$4:$G$2500,C11,'[Locations and Skills.xlsm]Staff Area'!$AK$4:$AK$1107, "Yes")

But I’m just getting the #VALUE! Result. (Also, you will see in my formula that I’m actually looking in columns G and AK but for the purposes of this query, just swap them for columns A and B).

Can anyone help me to achieve the result that I want?

Thanks very much!

 

[FormR]

=COUNTIFS('[Locations and Skills.xlsm]Staff Area'!$G$4:$G$2500,C11,'[Locations and Skills.xlsm]Staff Area'!$AK$4:$AK$1107, "Yes")

But I’m just getting the #VALUE ! Result.

Hi, you are getting #VALUE ! because the size of ranges are difference, i.e. you have the AK column finishing at row 1107 and the G column at row 2500 - these need to be the same.

I don't think you have described the "Staff Area" sheet completely - how do we know what skill is what based on the "Yes" in column B?

 

[snjpverma]

you can try using sumproduct function instead of Countifs. Something like this.

=SUMPRODUCT(--('[Locations and Skills.xlsm]Staff Area'!$G$4:$G$2500=C11)*('[Locations and Skills.xlsm]Staff Area'!$AK$4:$AK$2500="Yes"))

 

[Grimlocc]

Sorted! I just changed the size ranges and BINGO! It worked. Thanks very much FormR

Snjpverma - I tried the SUMPRODUCT one as well but it wouldn't work as soon as I put the -- in. I think maybe I'm supposed to use something else there as well as where you put the *?