Using Countifs to Check for Multiple and Exact Conditions

John Crick

Greetings! Reaching out to this group for assistance in creating a formula that counts the number of times a certain Product is associated with a given Account ID. The challenge is that the Account IDs may include the same characters but differ based on the case of a letter (a cap or small "a" at the end of the IDs shown below).

The objective is to find the quantity of Product A uniquely associated with Account ID 0011N00001FG8aA and uniquely associated with Account ID 0011N00001FG8aa. Similar formulas will be created to find the quantity of Products B and C for each unique Account ID as well.

Thank you for your assistance with this request.

John

Paul Newcome

In that case you are going to need to incorporate the FIND function as that is the only one that is case sensitive.

=COUNTIFS({Product}, @cell = "Product A", {ID}, FIND("0011N00001FG8aA", @cell)> 0)

Paul Newcome

Are you saying that an account ID ending in a capital "A" is to be treated as different from an account ID with the same prefix but ending in a lower case "a"?

John Crick

Hi, Paul. Yes, the IDs ending in capital "A" are to be treated separately from the IDs ending in lowercase "a".

The third-party system we use for account management creates unique IDs in this manner. The ability to differentiate an ID based on case allows them to significantly expand the number of unique IDs.

Paul Newcome

In that case you are going to need to incorporate the FIND function as that is the only one that is case sensitive.

=COUNTIFS({Product}, @cell = "Product A", {ID}, FIND("0011N00001FG8aA", @cell)> 0)

John Crick

Thanks, Paul! That seemed to work as needed! Appreciate the help and quick turn!

John