|

# » Counting the Number of Combined First and Last Names Matching Criteria in a Dynamic Range

Problem:

Range B2:C5 contains first and last names. The range currently consists of 4 names, but they are frequently added or removed.
We want to create a formula that will count the number of names matching specified criteria (both first and last names begin with the same letter) that will update upon each change in the range.

Solution:

Define three Names:
Insert  Name  Define, or press
Name: FirstName, Refers to: \$B\$1
Name: Rng, Refers to: \$B\$1:\$B\$100
Name: DynamicRange, Refers to the following OFFSET formula:
=OFFSET(FirstName,0,0,COUNTA(Rng))

Use the SUM, LEFT, and OFFSET functions as shown in the following Array formula, which will count the number of combined first and last names matching the above criteria in "DynamicRange":
{=SUM((DynamicRange<>"")*(LEFT(DynamicRange)=LEFT(OFFSET(DynamicRange,0,1))))}
Screenshot // Counting the Number of Combined First and Last Names Matching Criteria in a Dynamic Range

Rate This Tip
 1 2 3 4 5
Rating: 3.53     Views: 28395