Wildcard Character

[John]

Is there a wildcard character in Excel?

Specifically, I'm looking to search for all subdivisions from a major
division. My naming scheme is like this:

AppDev.DBA
AppDev.Web
Communications.Data
Communications.Radio

How can I just say AppDev.* or Communications.* so I can get a full
count??? (PS there is no standard length to any of these names)

Thanks

 

[Paulw2k]

From XL2002 Help files
Wildcard characters
The following wildcard characters can be used as comparison criteria for
filters, and when searching and replacing content.

Use To find
? (question mark) Any single character
For example, sm?th finds "smith" and "smyth"
* (asterisk) Any number of characters
For example, *east finds "Northeast" and "Southeast"
~ (tilde) followed by ?, *, or ~ A question mark, asterisk, or tilde
For example, fy91~? finds "fy91?"


Paul

 

[Frank Kabel]

Hi
do you mean
=COUNTIF(A1:A100,"AppDev.*")

 

[John]

Similar: =SUMPRODUCT(('Project Portfolio'!S3:S214="Proposed")*('Project
Portfolio'!T3:T214="AppDev"))

Where it shows AppDev, I want to to basically be AppDev.* but it doesn't
seem to work. Any ideas?

 

[Frank Kabel]

Hi
try:
=SUMPRODUCT(('Project
Portfolio'!S3:S214="Proposed")*(ISNUMBER(SEARCH("AppDev",'Project
Portfolio'!T3:T214))))

 

[John]

I got a #Ref error with that.

Basically I'm trying to count the number of projects per division per phase.
But my list of projects is divided into division/subdivision and phase.

Example data in the cells would be:

Project Phase Division

Project1 Proposed AppDev
Project2 Initiation AppDev.DBA
Project3 Execution Communications.Radio
Project4 Proposed Communications.Radio

So what I want is a count of AppDev projects by phase and comm projects by
phase. But just putting communications doesn't give me all comm projects.
So I need basically a wildcard character (or something that will roll all
comm.* projects up.
Thanks

 

[Frank Kabel]

Hi
a #REF error indicates that there's something wrong with the sheet
name. As I copy it from your original formula maybe a linebreak, etc.

 

[John]

Ok, I hammered out a few things, and it works like a charm!

Thank you. You have now saved me over 2 hours a week. Its much
appreciated!