(…or end with the same suffix..!)

This is another quick tutorial on one of the many applications of the =SUMPRODUCT function.

Sometimes you are working with a dataset and you are looking out in particular for a starting 4-number code, or 3-letter prefix, or GL codes that start with the same 2 numbers. The following is an example:

If you wanted to generate a count of rows that had values all starting with say, the same 5 numbers – 56003, the formula below would get the job done for you:

=SUMPRODUCT(–(LEFT(B3:B16,5)=”56003″))

This formula uses a combination of =SUMPRODUCT and =LEFT functions to extract the first 5 characters and use these to search through the range provided (B3:B16) and return the count of all that meet that criteria. This can also work by using the =RIGHT function to find those that end with the same 1 character, for instance, “6”. The formula for counting the rows that end with the number 6 is as follows:

=SUMPRODUCT(–(RIGHT(B3:B16,1)=”6″))

This formula not only works for values, but for text too. See an example below, where it is used in Column C to find all rows with text that start with the 4-character string “Best”

=SUMPRODUCT(–(LEFT(C3:C16,4)=”Best”))