Countifs formula does not recognise some words

MilanB

New Member
Joined
Feb 9, 2021
Messages
21
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
  2. Web
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: 12
Something funny here
Excel Formula:
=CODE(RIGHT(A41))
returns the ascci code for the last character in the cell & 112 is p not a ). Also it should not return a value of 3178.
 
Upvote 0

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
@Fluff
Generally, I wrote wrong, it is not 3178, but 3178,04 :D

I don't know what is exactly the problem. Can it be connected with formating cells?
 
Upvote 0
The code function returns a whole number up to (I think) 255
 
Upvote 0
But it is still strange that everything works normally when I change the name from "Medicala A (auto)" to "Medicala A auto"...
I wanted to upload a table on the forum, but I don't know how to do it.
 
Upvote 0
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

The other option would be to upload your workbook to a share site, such as OneDrive, GoogleDrive, DropBox, mark for sharing & than post the link you are given here.
 
Upvote 0
Ok. Let try then with GoogleDrive.


Strange. Now when I upload document in GoogleDrive, it works. Maybe it is connected with the saving format of the document? The document was saved as .ods.
 
Upvote 0
If it has an ODS extension, you are working with an OpenDocument Spreadhseet Document, and not an Excel file.
Those are important details that you want to be sure to mention when posting your questions!

I have never worked with ODS files before. I am guessing that you might need to convert it to an Excel file first.
A Google search shows that there are programs that will do that conversion for you.
 
Upvote 0
Sorry, I didn't look in which format is saved the file. I got from the company exported file in .xlsx format and now everything works normally.

I have one more additional question. In my document exist a file that represents insurances of the same type. For example "Medicala A (auto)" and "Agentie Turism A" are both medical insurances. Can I make some changes in my countifs formula to count all this same type in one category? For example to make a new category "Medical Insurances" and in it to count "Medicala A (auto)", "Agentie Turism A", "Agentie Turism B"... per agent. I hope I explain well what I want.
 
Upvote 0
How about
Excel Formula:
=SUM(COUNTIFS($A$2:$A$33,{"medicala A (Auto)","Agentie Turism A"},$I$2:$I$33,B$39))
This mat need array entry, depending on you version of Xl.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
Solution
Yes. Thank you. Exactly what was necessary.
And I have one more question about the same topic. I am not sure is it possible countifs function to skip in counting words that are marked in a different color.
Because this table I export sometimes has words that are red or blue. Red means annulated, blue not active or not realized. So these are insurances that are not supposed to be counted.
Sorry because of so many questions, but I try to write everything that I remember now, to not disturb every day with questions.
 
Upvote 0

Forum statistics

Threads
1,213,581
Messages
6,114,433
Members
448,573
Latest member
BEDE

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
Back
Top