# nesting with countif

R

#### 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")

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

D

#### 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.