nesting with countif

[Ross]

Hi,
I am having trouble with nesting the and command in a sheet I am
attmepting to prepare a spreadsheet to keep track of the clients in the
various programs at the not-for-profit blind services agency where I work.
I pasted a small piece of the sheet below. I used the countif function in
the formulas in column R below
=COUNTIF($S$25:$S$31,"Alp II", =COUNTIF($S$25:$S$31,"Alp III",
=COUNTIF($S$25:$S$31,"Alp II E", and =COUNTIF($S$25:$S$31,"Ancillary to add
up the how many people were in each program, but I have not been able to
figure out how to count for example Alp IIs, who have been closed. As you
can see at the bottom, I put in an example of one attempt to nest the "AND"
function with the countif, but Ecel said no. So can anyone tell me how to
build a formula that will count not only the total from each program as I
have done, but also the total for each program, whose case has been closed
(note the X under CL means client is closed.

O P Q R
S
Clients CL opened Closed
Program
Blow, Joe X 4/3/05 7/12/05
Alp II
Lincoln, Abraham 6/18/05
Alp III
Wifenpicle, T 7/14/05
Ancillary
Washington, G X 4/19/05 7/23/05 Alp II
E
Wilson, W. 8/8/88
Alp II
Truman, H. S. X 3/24/05 4/12/05
Ancillary

Total Alp II: 2 =COUNTIF($S$25:$S$31,"Alp II", (formula that
works)
Total ALP III: 1
Total Alp II E: 1
Total Ancillary: 2

(below is one of my miserable unsuccessful attempts at making the formula
=COUNTIF($p$25:$p$30,"X"), AND( $s$25:$S$30,"Alp II")




--------------------------------------------------------------------------------

 

[Dave Peterson]

One way:
=sumproduct(--($s$25:$S$30="Alp II"),--($p$25:$p$30="X"))

=sumproduct() likes to work with numbers. The -- converts trues/falses to
1/0's.