# Countifs formula does not recognise some words

#### MilanB

##### New Member
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.

#### Attachments

• 23.png
108.5 KB · Views: 10

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### 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

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

#### MilanB

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

#### MilanB

##### New Member

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:
Excel Formula:
``=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

##### New Member
Try entering this formula in any open cell and see what it returns:
Excel Formula:
``=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

##### MrExcel MVP, Moderator
If you do
Excel Formula:
``=CODE(RIGHT(A5))``
and the same for A41 what do they both return?

#### MilanB

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

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

 ​

Replies
3
Views
81
Replies
3
Views
119
Replies
8
Views
115
Replies
3
Views
230
Replies
4
Views
162

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,151,825
Messages
5,766,663
Members
425,367
Latest member
Boboka

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back