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:
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.
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:
Step 3: Input the required values in the formula
The following formula requires range as well as criteria.
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.
Next, we will move to the criteria. As you can observe in the image above, we have written the following:
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.
And BAAAMMM!!! There you go.
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.
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.
It’s easy, isn’t it😉?