In this space, we will learn how to count data that ends with a particular value e.g. RSVPUP-12-P, here, P is the ending value of the cell.

When you are dealing with loads of database, and you are looking for a specific data entry🤓, it becomes nearly impossible to scroll and look into every data entry in the sheet😶. Not just that, the situation becomes more difficult when you realise that you don’t remember the whole thing😣, all you remember is the one little thing from it which later becomes our wildcard😎.

But for the time being, you are screwed up😪!!!

So, let’s get started by elaborating the whole procedure through the help of an example:

count data that ends with

Step 1: Analyse the data

As you can see in the image above, we have a bunch of product codes and we need to find the product code number which are ending with P as well as L.

count data that ends with

Step 2: Input the formula =COUNTIF(

To find the count of product codes as per some specifications, we will use the help of COUNTIF Function i.e. as follows:

=COUNTIF(

Step 3: Input the required values in the formula

The following formula requires range as well as criteria.

count data that ends with

So, as we begin with the first step in the formula, i.e. range, we will select all the product codes available to us because our lookup value is a product code only. So, the range in the above example becomes B2:C12.

(PIC4)

Next, we will move to the criteria. As you can observe in the image above, we have written the following:

“*P”

We have used quotation marks as we are adding specifications in the formula, and the asterisk is used to play the role of wildcard. As we have mentioned this word before, here’s the deal about it:

Step 4: Use the wildcard

Wildcards are basically those symbols which help us to identify a specific number or a character. Wildcard can be:

  • Asterisk (*)
  • Question mark (?)
  • Tilde (~)

So, as we are looking for the count of product code ending with P, we will use our wildcard with it to get back in the game😎!!!

*Please note: As you are adding some specifications to the formula, it is necessary to jot down the criteria in quotation marks.

count data that ends with

And BAAAMMM!!! There you go.

count data that ends with

Step 5: Use the same procedure for L code

The same goes with the case of L code.

We will add the COUNTIF Function, we will select the range which will remain the same as the lookup value is product code only (B2:C12) and our criteria will be “*L”.

*Again, please note that you have make the use of quotation marks as you are adding specifications and do not forget to add the wildcard or else the formula will not work out as it should be.

count data that ends with

There you go with your answers.

The total count of products ending with P code are 3 in number and the total count of products ending with L code is 1 in number.

count data that ends with

It’s easy, isn’t it😉?