» Counting the Number of Combined First and Last Names Matching Criteria in a Dynamic Range
CATEGORY - Excel Array Formulas
VERSION - All Microsoft Excel Versions
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))))}
Book Store:
Recommended Books:
- Accounting Principles, with CD, 6th Edition
- Business Analysis and Valuation: Using Financial Statements, Text and Cases
- Now, Discover Your Strengths
- Special Edition Using Microsoft Word 2002
- Business Plans Kit for Dummies (With CD-ROM)
- H&R Block's Just Plain Smart(tm) Tax Planning Advisor: A year-round approach to lowering your taxes this year, next year and beyond
No comments have been submitted.
