Countifs is not distinguishing between 1.1 and 1.10

[tommyk203]

I'm having a problem with the below spreadsheet. Sheet1 contains values 3.1 through 3.11. Sheet2 counts # of times each Domain (eg, 1a,1b,1c) is present for each Standard (eg 1.1, 1.2, 1.3.)

The problem i am having is that when my countifs statement in sheet2 3b and 12b, looks for 3.1 and 3.10 respectively, both 3.1 and 3.10 are coming back as matches. How can I avoid this and have the count for 3.1 and 3.10 come up separately?


Thank you!

https://docs.google.com/spreadsheets/d/1qLwvlXW0xYs_cVfhAEUpQg8jZFOAZBQoTVPFDOTTRH0/edit?usp=sharing

 

[Michael M]

As far as numbers go

both 3.1 and 3.10 are coming back as matches.

they are exactly the same.....you may have to consider changing your numbers to text !!

 

[Aladin Akyurek]

What is the formula you use? Also, care to post a sample on this board directly instead of a link?

 

[tommyk203]

Here is the formula i am using in sheet2, b3. =COUNTIFS(Sheet1!$A:$A,$A3,Sheet1!$E:$E,"*"&B$2&"*")

 

[tommyk203]

Here a link to download the excel file
https://drive.google.com/uc?export=download&id=0B5rAEFrMWLkscUMyaEprUURzU1E

 

[Aladin Akyurek]

Here a link to download the excel file
https://drive.google.com/uc?export=download&id=0B5rAEFrMWLkscUMyaEprUURzU1E

Please try to post an Excel readable, small sample, using one of the methods listed in the following: Attachments or using this add-in:https://app.box.com/s/soezox25h3w0q5s4rcyl instead of providing an outside ephemeral link to a file.

 

[Aladin Akyurek]

Here is the formula i am using in sheet2, b3. =COUNTIFS(Sheet1!$A:$A,$A3,Sheet1!$E:$E,"*"&B$2&"*")

Try rather...

=SUMPRODUCT(--(Sheet1!$A$2:$A$400=$A3),--ISNUMBER(SEARCH(","&B$2$",",","&SUBSTITUTE(Sheet1$E$2:$E$400," ","")&","))

 

[tommyk203]

This link should work to download the excel file.
https://drive.google.com/uc?export=d...UMyaEprUURzU1E

 

[tommyk203]

@Aladin - That did not appear to work

 

[RoryA]

Cell A11 on Sheet 1 contains 3.1, not the text "3.10". It has simply been formatted to display 3.10 (if you check the formula bar you can see the actual cell value).