Countifs formula does not recognise some words

MilanB

New Member
Joined
Feb 9, 2021
Messages
17
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
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
    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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,820
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
67,955
Office Version
  1. 365
Platform
  1. Windows
Check that you don't have any leading/trailing spaces in A41 and in the relevant cells in A2:A33
 

MilanB

New Member
Joined
Feb 9, 2021
Messages
17
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
@Joe4 @Fluff
Thanks for the answers. This is the first thing I checked. No additional spaces between words or behind.
 

MilanB

New Member
Joined
Feb 9, 2021
Messages
17
Office Version
  1. 365
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,820
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,820
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Feb 9, 2021
Messages
17
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
67,955
Office Version
  1. 365
Platform
  1. Windows
If you do
Excel Formula:
=CODE(RIGHT(A5))
and the same for A41 what do they both return?
 

MilanB

New Member
Joined
Feb 9, 2021
Messages
17
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
When I write =CODE(RIGHT(A5)) I get - 3178,00

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

 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top