0

I am working on a password audit, and one of the tasks I'm trying to solve is counting the number of instances a username is present in the password. For instance, the username might be 'mikeb' and their password is 'mikeb123'.

Searching for a username in a password is simple enough: COUNTIF(A:A, "mikeb")

The problem I'm running into is how to I check A1 against B1, A2 against B2, for the entire column, and add up the number of times that B contained A.

Currently I'm using a workaround where I make the comparison then count the number of true values in a separate column. I'd like to get away from another column if possible.

EDIT: Per request, dummy data:

Username    Password              Password Contains Username?
Bob         BobHasASneakyPa$$word TRUE
Carol       No1LikesUCarol        TRUE
Admin       <>@@Admin@@<>         TRUE
Brian       ;Ui6$m8/4??k3&)r7     FALSE

This is what my data looks like right now. I am using COUNTIF(A2, "*" & B2 & "*")>0 for the third column, then doing COUNTIF(C:C, "TRUE") to count up the # of times this happens. Ideally I'd combine these into one equation.

Tchotchke
  • 399
  • 1
  • 2
  • 18

1 Answers1

1

Try using

=SUMPRODUCT(--ISNUMBER(SEARCH(A2:A5,B2:B5)))

I've tested this on your dummy data and searched for the username in the password. It returns an answer of 3 which would be the same as summing your third column.

You could also make this case sensitive if needs be by changing SEARCH to FIND

Tom
  • 9,725
  • 3
  • 31
  • 48