Ignore that post.

total mental aberration - time for bed!!!!

--
Regards

Roger Govier


"Roger Govier" wrote in message
...
Hi

Try
=SUMPRODUCT(($A$17:$A$24={"ICS," "474", "1390"})*
(NOT(ISNUMBER(SEARCH($B$17:$B$24,"W")))))


--
Regards

Roger Govier


"WiFiMike2006" wrote in
message ...
Ok. Now here's a similar problem, but a little more complicated.

For example, let's say this is the data range:

A B C D
16 cam unit
17 PG W12
18 ICS X5
19 ICS W22
20 474 X7
21 474 W15b
22 1390
23 1390 W6
24 PG

What would I need to do to get a count in cell D16 of only the number
of
ICS, 474, and 1390 in the "cam" column that DO NOT have text
*containing* the
letter "W" in the "unit" column? For this example, the result in D16
would be
3.

I hope you can answer this one too.

Thank you,
Mike

"Ron Coderre" wrote:

Sorry about the confusion....

Regarding: =DCOUNTA(N16:S120,"List_1",K1:L2)

Excel has several functions specifically made to work with data
lists
("databases"). DCOUNTA returns the count of non-blank items in a
specified
column of a database that match a criteria.

It has this format:
=DCOUNTA(database,fieldname_to_count_values_in,ran ge_containing_criteria)

In your example....you have 2 lists of values:
N17:N120
and S17:S120

Since database need column headings,
I arbitrarily put "List_1" in N16 and "List_2" in S16

For the first parameter of the function
I used N16:S120
....Since we're not using columns O,P,Q and R
It doesn't matter what's in them or that they don't have column
headings

For the second parameter of the function
I entered "List_1"...the column heading of the range to count

For the third parameter, the criteria,
I referenced the range K1:L2
....That range must have a certain format.
The top row of cells MUST contain a column title from the database
The cells below those titles contain the pertinent patterns to
match.
K2: EP.....so List_1 items must only have "EP" in them
L2: *migrate*....so List_2 items must *contain* the word "migrate"

(criteria can get somewhat complicated but your needs did not
require
anything fancy)

Summary:
the formula starts with the database: N16:S120
Finds all instances where List_1 = "EP" AND List_2 contains
"migrate"
and returns the count of non-blank items from List_1

I hope that helps.
***********
Regards,
Ron

XL2002, WinXP


"WiFiMike2006" wrote:

I'm not quite sure what I need to do for this to work. The ranges
i gave in
the original post have various text in all the cells. Are you
saying I need
to enter a list of something in certain cells? Can you please give
me a
little more of a step-by-step on this? I've never even heard of
DCOUNTA
before, let alone used it.

Thanks!
Mike

"Ron Coderre" wrote:

Another possibilty....DCOUNTA?

With
N16:N120 contains your first list, with List_1 in N16
S16:S120 contains your second list, with List_2 in S16

K1: List_1
K2: EP

L1: List_2
L2: *migrate*

M1: =DCOUNTA(N16:S120,"List_1",K1:L2)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"WiFiMike2006" wrote:

Hello,

I am trying to create a formula that will count the number of
cells with
certain text (EP) in one range that also have an instance of
certain wildcard
text (*migrate*) in another range in the same row.

For example, this is the formula I'm using, but it doesn't
seem to work, and
I need to know what I should use instead:

=COUNT(IF((N17:N120="EP")*(S17:S120="*migrate*"),0 ))

However, the formula works if it looks like this (wildcard
text not used for
"migrate"):

=COUNT(IF((N16:N101="EF")*(S16:S101="migrate"),0))

But, the reason I need to use the wildcard text is that
sometimes the text
in S16:S101 will be changed to "migrated" or "replaced &
migrated", which
throws off the count resulting from the formula.

Is there a way to do this with a different function or
formula? Apparently,
the wildcard text thing only seems to work for criteria in
simpler formulas
like this one:

=COUNTIF($O$16:$O$105, "*SWM*")

Any help would be greatly appreciated. I've spent hours trying
to figure
this one out.

Thank you!

Mike