» 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:
- Your First Business Plan: A Simple Question and Answer Format Designed to Help You Write Your Own Plan (3rd Ed)
- Who Moved My Cheese? An Amazing Way to Deal with Change in Your Work and in Your Life
- Advanced modelling in finance using Excel and VBA
- Microsoft Excel VBA Programming for the Absolute Beginner
- The Guide to Understanding Financial Statements
- The South Beach Diet: The Delicious, Doctor-Designed, Foolproof Plan for Fast and Healthy Weight Loss
No comments have been submitted.
Excel Formula and Function
Excel Date and Time
Excel Macros and VBA
Excel Text, Editing and Format
Excel General
Excel Data
