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
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
