» 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:
- East of Eden (Oprah's Book Club)
- Accounting and Financial Fundamentals for Nonfinancial Executives
- Managing by the Numbers: A Commonsense Guide to Understanding and Using Your Company's Financials: An Essential Resource for Growing Businesses
- MP Managerial Accounting w/ Topic Tackler, Net Tutor, & PowerWeb
- Cashflow Quadrant: Rich Dad's Guide to Financial Freedom
- The Accounting Game : Basic Accounting Fresh from the Lemonade Stand
No comments have been submitted.
