lifehack

Excel tip

=COUNTIF(A:A,CONCATENATE(“*”,A14,”.*”))

Two things,

1. “A:A”.

This will cover all the rows in A column.

2. CONCATENATE(“*”, A14, “-*”)

This will be make a string which combine “*” , the value of cell A14 and “.*”.

Let’s guess the A14 has “MBP”, A15 has “MBP-13”, A16 has “MBP-15” and A17 has “MBA-11”.

In this case the above will give 2 – “MBP-13” and “MBP-15”.

If the matched string is always “MBP”, it is equivalent to

=COUNTIF(A:A,”MBP*”)

But if matched string is stored on a specific cell, we should change the function. With the first function, we can just change the reference cell number which has the matched string.

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s