Excel Name Trick #6: Names COUNTIF & wildcards

I'm trying to develop a table within SQL server that is generated by combining the contents of up to 1000 other tables, all with similar names. Is there any way you can write a query in SQL that will append together all tables that begin "dbo_S" and ends "KWH"?
I basically want to be able to query [dbo_S*KWH].[Value] and look up the value field on any table that fits the criteria.

Is this even possible?

hi there.

i have a column of telephone numbers taken from a program we use which gives us a raw data.

all i am trying to do for the last 2 hours is to do a count of all cells where the number is starting with "7".

I used the formula: =COUNTIF(A1:B8,"7*")

It just keeps coming with zero as the result. Not sure whether its to do with the formatting of the cells.

PLEASE HELP!!!!

I would like to have a COUNTIF function that would count every cell that contains either a 1 alone, or a 1 in combination with other characters.

The expression:

Code:

=COUNTIF(AG11:EQ11,"*1*")

in practice is ONLY counting a cell that contains 1 in combination with other characters (such as sus1). It is not counting any cells that simply contain the value 1.

For now I am using the workaround:

Code:

=COUNTIF(AG11:EQ11,"*1*")+COUNTIF(AG11:EQ11,"1")

but I think there should be a way to use only one argument to achieve the results I want.

Why is the first expression coded above NOT counting pure values of 1, and what can I do to improve the expression so that it will do it in one step instead of two?

I have a spreadsheet with 3 columns. All columns are text formatted. I want to count the number of entries in column C IF column B equals Associate. I've tried using the "*" wildcard for the value in column C in various formula's but it does not work. Any help is very much appreciated.

Column A = list of names (EX: Smith)
Column B = membership type (EX: Associate, Life)
Column C = members state of residence (EX: GA, KS)

I know it doesnt work, and iv'e been looking for a solution and i couldnt get it too work, so now i'll post everything. I have excel 2003.

I have a list that looks like this, its in a column:


3. NORWAY BLU-RAY MEDIUM 3. NORWAY DVD KIDS MEDIUM 3. NORWAY DVD MEDIUM 4. NORWAY TV-SERIES LARGE



And this list is huge and contains many more countries, and many more sizes etc.

I want to count how many instances of the the words "BLU-RAY" and "MEDIUM" when they are together in a cell - from the liste above it would be 1.

The data comes from a file that is called replenishment.xls and is located in column G. If i have to insert 1's in the H column to get it working please do tell - i am a complete newbie.

Suppose that I have the following data spread across several columns:

Red, Black
Green, White, Orange
Blue
Green, Orange
Yellow, Blue
Purple, Brown

I wanted to get a count of the number of items that contained the word "Blue", so I used the following formula:

=COUNTIF(G5:H7,"*"&F10&"*")
where G5:H7 contained the values from above and F10 contained the word "Blue".

I also have a column with numeric values, so I am looking to create a similar formula that sums these values based on the same criteria as the COUNTIF. I thought this formula would work:

=SUMIF(G5:H7,"*"&F10&"*",D5:D7)
where D5:D7 contains the numeric values

However, this only returns the first value it finds rather than the sum. I tried setting the formula as an array but I get the same result.

I have a feeling this is a simple one but I just can't figure it out.
Thanks for your help.

Is it possible to count the number of times a value appears in a range using a cell ref as the criteria, but the value will only be part of a string in the cells in the range?

in other words:

range values:
A , B
"1 v 2" , "11 v 1"
"3 v 4" , "12 v 3"
"1 v 3" , "1 v 13"
"2 v 4" , "12 v 11"

I want to count how many times "1" appears in the range? And 2, and 3, etc... so I can generate a list like this:

C,D
1,4
2,2
3,3
4,2
11,2
12,2
13,1

Experimenting, I came up with a very non-elegant solution that kinda works, but I'm hoping there's a better way:

C,D
1,=countif($A$1:$B$4,"1 v*")+countif($A$1:$B$4,"*v 1")
2,=countif($A$1:$B$4,"2 v*")+countif($A$1:$B$4,"*v 2")

and so on...

I'd prefer to have something more like:

1 =countif(<range>,"<the part I can't figure out with wildcards>" & C1)

in other words, how do I say, "count if the value of my reference cell is contained within the text of a cell in the range" ??

I have both 2003 and 2007 available. Not sure if this is possible, but I'd appreciate the help!

TIA

Hi,

I am trying to count the total of occurrences of a particular word in one column and then count the total of occurrences of another word in another column to give one total.

Would anyone know what is the best formula to use. I will have to incorporate wildcards into the formula also. Both columns contain for example: controlled, number controlled etc and would like to count all the cells which contrain the word controlled in.

Any help would be much appreciated.

Cheers,

Tony

Hello I am currently trying to use Excel 2003 Data validation to prevent duplicate data Entry.

The data I have for example in A1:A4 is as follows

04-20-026-22W4/2
16-33-025-22W4/2
07-27-026-22W4/0
04-20-026-22W4/0

In the data validation I have custom and this for the formula

=COUNTIF($A$1:$A$4,A1)<=1 for cell A1

My problem arises in that I need it to do this lookup based on the wildcard for the last number.

So that cell A1 = A4 and therefore will not let me put in 04-20-026-22W4/0 or /2 or anything else.

So something similar to 04-20-026-22W4/* where * = wildcard.

The cells are currently formatted as General and I have tried a few variations of countif and sumproduct with no luck.

Thanks in advance for any help you can offer.

When you are making a formula say

=A5 + A6+A7

when you are doing this in cell D423, it sometimes gets annoying to have to either type in A6 and A7 manually or to navigate to A6 and A7.

I know there is a key you can hold down after doing A5 that keeps you on A5 even after putting in the + sign, so that you don't have to navigate all the way back there.

I can't figure it out!

Does anyone know what this is?

Please help!!

Thanks,
Michael