SUM with two conditions

[Art]

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

 

[T. Valko]

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

 

[T. Valko]

Ther's only a few functions that support wildcards:

I forgot about Excel 2007.

SUMIF - all versions of Excel
COUNTIF - all versions of Excel
SEARCH - all versions of Excel
MATCH - all versions of Excel
HLOOKUP - all versions of Excel
VLOOKUP - all versions of Excel
SUMIFS - Excel 2007
COUNTIFS - Excel 2007

This formula will work in any (modern) version of Excel:

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

If you're using Excel 2007...

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

 

[Art]

You're awesome...thanks!

I forgot about Excel 2007.

SUMIF - all versions of Excel
COUNTIF - all versions of Excel
SEARCH - all versions of Excel
MATCH - all versions of Excel
HLOOKUP - all versions of Excel
VLOOKUP - all versions of Excel
SUMIFS - Excel 2007
COUNTIFS - Excel 2007

This formula will work in any (modern) version of Excel:

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

If you're using Excel 2007...

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

--
Biff
Microsoft Excel MVP





.

 

[T. Valko]

You're welcome. Thanks for the feedback!