Excel Name Trick #6: Names COUNTIF & wildcards

[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)

[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!

[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!

[blakflash]

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

[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.

[kcarpen3]

Thank you for these videos, they have been a great help in my Info Management class.

[ExcelIsFun]

You are welcome!

[jaialaipro34]

You are a freagin genius! I have been trying to learn how to mix wildcard with cell locations for months and could not figure it out. I will watch all of your videos. Thank you so much!!!!

[ExcelIsFun]

You are welcome. If you go to the excelisfun channel, a video will automatically play that shows how to search for the videos, playlist and Excel workbboks that you need to solve whatever problem you have.

[microsuez]

You have made such a wonderful collection of excel videos. I wonder whether it is a good idea for you to put a link to the iGoogle gadgets so that more people will enjoy your videos. You may have title like " Excel tip of the day", " Daily Excel ", " Amazing Excel tricks to use before you die ". Let me know, if you decide to do so, i will be the first to subscribe!!