Search code examples
excelexcel-formulacountcountif

Count the number of cells that contain a string, but not when that string is attached to a certain letter

I have a table here:

A B
Red I.A.3, I.C.2, I.A.2
Blue I.A.2, I.A.3
Blue I.C.1
Red II.A.1, I.A.2, I.B.2
Red I.C.1, II.A.2
Blue I.A.1

And I want to make a single formula to count the number of "Red" row cells that contain "I.A" at least once. So in the above case, the formula would result in 2.

The problem I am having is getting it to count only cells that contain "I.A" but not "II.A" if it doesn't also include "I.A". So in a sense, I need to count the Red cells that contain "I.A" at least once but not if the only occurrence is one of "II.A".

The issue is they contain the same characters, except II.A has an extra I to the left.

I've tried COUNTIFS() but that was giving me all values including II.A exclusives.

My latest formula is:

=SUM(IF(A:A="Red", IF(ISNUMBER(FIND("I.A", B:B)) * NOT(ISNUMBER(FIND("II.A", B:B))), 1, 0)))

However, this formula results in the count not including the cells where "I.A" and "II.A" are both present. I need to somehow check if another "I" is not there. This would be easier with Regex, but I don't think I can do that on excel (cannot add macros on work computer).

Solution

  • IIUC, you can do this with SUMPRODUCT, appending a , to the text, and then looking for , II.A but not , I.A, then subtracting that result from the count of just I.A.

    =SUMPRODUCT((A2:A7="Red")*(ISNUMBER(FIND("I.A",B2:B7))))-SUMPRODUCT((A2:A7="Red")*(ISNUMBER(FIND(", II.A",", "&B2:B7)))*(NOT(ISNUMBER(FIND(", I.A",", "&B2:B7)))))

    enter image description here

    EDIT:

    No need to append comma and space, just a space will do:

    =SUMPRODUCT((A2:A7="Red")*(ISNUMBER(FIND("I.A",B2:B7))))-SUMPRODUCT((A2:A7="Red")*(ISNUMBER(FIND(" II.A"," "&B2:B7)))*(NOT(ISNUMBER(FIND(" I.A"," "&B2:B7)))))