Trouble with COUNTIFS in a range

[RT2019]

Hello, Sorry if this has been asked I've searched the site but not found an answer.

I am trying to pull a count of a 'word' occurrence based on another 'word' criteria. I have a range of 3 columns by 148 entries and similar amounts on 3 other sheets.

So I am trying to find a concise formula. The ranges are all named (correctly I think).

Using only the first sheet as a test I use the formula:

=COUNTIFS(WS1_code,$A15,WS1_code.att, AS$3)

Which produces a count of 7, the correct count is 8

The range WS1_code is ='WS1 Framework Data'!$J$2:$L$149 and WS1_code.att ='WS1 Framework Data'!$N$2:$P$149
The ranges are the same size and the cells A15 and AS3 both reference a word. The words are from drop down lists to avoid spelling issues. They are meant to be drag-able hence the Use of $ only where they are.

The following formula produces a count of 8, however if I try to replicate it to keep stacking COUNTIFS to include all three columns WS1.1_code, WS1.2_code, WS1.2_code across 4 sheets the formula produces an error.

=COUNTIFS(WS1.1_code,$A15,WS_1_AT1, AS$3)+COUNTIFS(WS1.1_code,$A15,WS_1_AT2, AS$3)+COUNTIFS(WS1.1_code,$A15,WS_1_AT3, AS$3)

I'm not an excel wizard and I'm sure there is a more elegant way to do this unfortunately I don't know that way. I haven't uploaded the sheet as its my research data and I'd have to anonymise it first, sorry.

Any help would be very very much appreciated.

Thank you in advance.

-R

 

[steve the fish]

I would think there is indeed a spelling mistake somewhere. More than likely in your data. Check for trailing spaces. They arent easy to spot.

 

[RT2019]

I would think there is indeed a spelling mistake somewhere. More than likely in your data. Check for trailing spaces. They arent easy to spot.

Thank you for your reply and suggestion, I've re-checked for trailing spaces. The data is entered using data validation formatting so each entry/criteria is from chosen from a drop-down list. I was wondering if you couldn't use the ranges in that manner.

 

[steve the fish]

The part where you wrote about it producing a count of 7 when you expected 8 really does make me think thats a data issue rather than the formula. If the formula is no good then it will just error.

Using only the first sheet as a test I use the formula:

=COUNTIFS(WS1_code,$A15,WS1_code.att, AS$3)

Which produces a count of 7, the correct count is 8

 

[RT2019]

The part where you wrote about it producing a count of 7 when you expected 8 really does make me think thats a data issue rather than the formula. If the formula is no good then it will just error.

I see your point about the error message, however the 2nd formula returns the correct count using the same cells as range criteria. Wouldn't a spelling error or data validation issue react the same in the second formula? Some counts are off by three as opposed to one and the word is simple "Trust". Again entered through a drop-down list. It doesn't make sense to me. I was wondering if the way it reads the range (column vs. array) had something to do with it but it is beyond my understanding.