=SUM((Textbooks!$A$4:$A$119="YES")*(Textbooks!$T$ 4:$T$119="*"&A22&"*"))

Ther's only a few functions that support wildcards:

SUMIF
COUNTIF
SEARCH
MATCH
HLOOKUP
VLOOKUP

Try this...

Normally entered

=SUMPRODUCT(--(Textbooks!$A$4:$A$119="YES"),--(ISNUMBER(SEARCH(A22,Textbooks!$T$4:$T$119))))

--
Biff
Microsoft Excel MVP


"Art" wrote in message
...
I have been struggling with this for awhile, so I decided to post.

I am creating a spreadsheet with two sheets. One sheet lists courses we
offer, while the second sheet lists textbooks. I want to write a formula
in
the first sheet that will count how many textbooks on the second sheet are
assigned to a given course. In the second (textbook) sheet, I have a
column
that I put the courses to which are assigned each textbook.

COLUMN A
Includes "YES" if the textbook is still actively used or "NO" if it is
discontinued.

COLUMN B
Title of textbook

COLUMN T
Courses (e.g., "PSY 250", "PSY 260, PSY 250", "")

There are three possibilities:

(1) No textbooks are assigned to that course. (No course IDs in Column T)
(2) A textbook is assigned to just the one course. (Only one course ID in
Column T)
(3) A textbook is assigned to more than one course, including the course
in
question. (More than one course ID in Column T)

So, I came up with this formula, which I typed in cell A22 of sheet 1:

=SUM((Textbooks!$A$4:$A$119="YES")*(Textbooks!$T$4 :$T$119="*"&A22&"*"))

What does this formula intended do?

After I pressed "Shift"+"Control"+"Enter", it checks that two conditions
are
met for each row in the second sheet. First, the row must include "YES" in
Column A and the row must include the course number in the current cell
A22
(e.g., PSY 350). I needed to use wildcards cause in some cases, Column T
may
include "PSY 350", "BUS 280, PSY 350", "PSY 350, BUS 280", "PCC 344, PSY
350,
BUS 280", etc. So, I wanted to count not only those just with PSY 350, but
any other row that contains PSY 350.

What's the problem?
If I replace the wildcards and cell A22 and only use "PSY 350", it counts
the PSY 350 instances, but does not include instances when PSY 350 is not
the
only course in a cell (e.g., "PSY 350, BUS 280"). It correctly finds only
the
PSY 350 in Column T when "YES" appears in Column A.

I am sure I am using wildcards incorrectly.

Any suggestions???

Thank you VERY much!!!


Art