Excel Name Trick #6: Names COUNTIF & wildcards

See how to use the Names & Wildcards with the COUNTIF function. Search through a list of Addresses and count just the ones on Birch Street. Search through a list of Addresses and count just the ones on the 9000 block of Birch Street.

See the wildcard *
See the wildcard ?

This is a logical (beginning to end) story about most of the use for Excel Names. In this series you will see 12 amazing uses for Excel Names (14 total Name Tricks).

Category:

Science & Technology

Tags:

• Excel
• Names
• Wildcards
• with
• the
• COUNTIF
• function
• Mike
• Gel
• Girvin
• Highline
• Community
• College

[batlin]

Nice shortcuts here! But is there a way to transform the cells examined in the criteria before applying it?

For example, if there was a temporary name (e.g. TestValue) for the current cell being tested by the criteria, I'd like to do:

=CountIf(A1:A100, WeekDay(TestValue) = 1)

...to count all Mondays in a range of date values? Without a placeholder for the tested cell value, I can't think of a way to do this other than by making a new column containing WeekDay(...) of every value in the range.

[ExcelIsFun]

@batlin , maybe a formula like:

=SUMPRODUCT(--WeekDay(TestValu­e) = 1) or =SUMPRODUCT(--WeekDay(A1:A100) = 1)

[Rebuild8]

Hi! One thing I noticed is that this formula will also count Birchwood. A way to remedy this is to just add a space in the quotation marks like so: "* "&C8&" *". You can also use wildcard arrays with countif's too.

Smith, John

Vikki L. Smith

Smithers, Jim

=SUM(COUNTIF(A2:A20,{"","* "}&C1&{",*",""}))

Typing Smith in C1 will return a 2.

[ExcelIsFun]

Nice catch!!!

Thanks for "robustifying" the formula!

[42FTA]

This is exactly what I needed - and more, thank you so much!

[ExcelIsFun]

You are welcome!

[batlin]

@ExcelIsFun Hi thanks, you're spot on... I figured it out after about 30 mins of headscratching:

=SUMPRODUCT(--(TEXT($A$1:$A$10­0, "ddd")=C3)*$B$1:$B$100) ... (where C1:C5 are "Mon"..."Fri", and I'm trying summing Bx to have a subtotal broken down by day)

The criteria syntax is hackish... Since you can directly map from what I wanted into this SumProduct, it's strange that there's no placeholder/variable syntax in Excel... SumIf(A1:A100, Text(TestValue, "ddd")=C3, B1:B100) would be much simpler!

[ExcelIsFun]

You are welcome! If you want to learn Excel, watch the 23 videos in the Basic Series, then move on to the 59 videos in the Highline Excel Class Series.

[blakflash]

Your excel series is awesome!! Everything I learnt in a $600 excel course is clearly explained in your series. Keep it up!!!!