Countifs formula does not recognise some words

[MilanB]

Hello. I used countifs formula to calculate how much some insurance agents sold types of insurance. In column A are types of insurance and in column I are names of agents. So I made countifs formula in B40 - =COUNTIFS($A$2:$A$33;$A40;$I$2:$I$33;B$39) to make calculation in another table. And formula counts all insurances, except one type, which is called "Medicala A (auto)". Can you help me to understand where I made mistake and why only this one type of insurance countifs function cannot see? In the attachment, I sent printscreen of tables.

 

[Joe4]

Make sure that the values are EXACTLY the same.
Anything as simple as an extra space at the end of the phrase "Medicala A (auto)" is enough to make it not match.

 

[Fluff]

Check that you don't have any leading/trailing spaces in A41 and in the relevant cells in A2:A33

 

[MilanB]

@Joe4 @Fluff
Thanks for the answers. This is the first thing I checked. No additional spaces between words or behind.

 

[MilanB]

One more thing. Right now I tried to change manually all into one word "MedicalaA(auto)" and still the problem is exactly the same - does not recognize only that category.

 

[Joe4]

Try entering this formula in any open cell and see what it returns:

=A5=A41

If it does not return TRUE, then the value in your summary section does not match the data for the "Medicala A (auto)" entries.
Something is different. It might use non-breaking spaces instead of normal spaces.
One way to correct that would be to simply copy the value from A5 down to A41, to make sure they match.

 

[Joe4]

If the above does not fix it, then look for the same issues with your column I entries for these records, and compare them to the headers in row 39.

 

[MilanB]

Try entering this formula in any open cell and see what it returns:

=A5=A41

If it does not return TRUE, then the value in your summary section does not match the data for the "Medicala A (auto)" entries.
Something is different. It might use non-breaking spaces instead of normal spaces.
One way to correct that would be to simply copy the value from A5 down to A41, to make sure they match.

And it is TRUE. I made a copy of the values and still is the same...

But now I did something else, change the name from "Medicala A (auto)" to "Medicala A auto" and now the formula works... Is it possible that brackets are the problem? This table is exported from the company accounting program so I cannot change it manually every single time because this table can be much bigger and include even information for few months.

 

[Fluff]

If you do

=CODE(RIGHT(A5))

and the same for A41 what do they both return?

 

[MilanB]

When I write =CODE(RIGHT(A5)) I get - 3178,00

But for =CODE(RIGHT(A41)) I get - 112,00

​