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

 

[tommyk203]

@Rory - I understand that. Ive tried formatting as text as well and still can't get it too work. Do you know how I can get it to work if it is text?

Thanks!

 

[RoryA]

If you change that cell to '3.10 then all you need is to correct a couple of typos in Aladin's posted formula:

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

 

[Jonmo1]

The value in Sheet1 A11 is NOT 3.10
It's actually 3.1, but you have it formatted to show 2 decimal places.

You need to format column A on Sheet1 as TEXT, and re-enter those values.
Then try this in Sheet2 B12

=SUMPRODUCT(--(Sheet1!$A$1:$A$12=$A12),--ISNUMBER(SEARCH(B$2,Sheet1!$E$1:$E$12)))

 

[tommyk203]

@ Jonmo and @ Rory

Thank you so much. Both of those formulas seem to work. Would both of you agree that one formula is superior to the other? Does one formula take other things into consideration. Thank you again for your help! Much appreciated!!

 

[RoryA]

For the sample you've posted, they will both do the same. Aladin's is possibly safer in that if you have a domain 11a for example, his formula will not match 1a to it, whereas Jonmo's will. I don't know whether that is actually a possibility for your data - if not, Jonmo's will probably calculate slightly faster.

 

[Jonmo1]

I don't think all the extra concatenating and substitute is necessary for the search of B2 in column E.
It's intention is to avoid a false positive match.

If say you had 1a and 1aa

1a would be a match to 1aa with my formula.
But it doesn't appear that any of the given strings in B2 and column E would have that issue..

 

[Jonmo1]

Aladin's is possibly safer in that if you have a domain 11a for example

Ah yes, I was only thinking of 1aa being a possiblity.
But 11a would be an issue, as 1a would be a match to 11a using the search.