I'm using Excel to maintain records for participants in a multisite clinical
trial of therapy groups. I'd like to use a function that filters records
according to specific criteria so that I get a count of the records meeting
these criteria. I have a column in which I have recorded the research site
(Site A=1, Site B=2). Another column records the experimental condition to
which participants have been assigned (Control=0, Treatment=1). Therapy
group has been recorded in another column (Group 1=1, Group 2=2, etc.).
Here's and example of what the data looks like:

ID Site Condition Group
1 1 1 1
2 1 1 1
3 1 0 1
4 1 1 1
5 2 0 1
6 2 0 1
7 2 1 1
8 2 0 1

A table I could create to provide a breakdown of the number of participants
at each site in each group in each therapy group would look something like
this:

Site A
Group 1
Treatment 3
Control 1

Site B
Group 1
Treatment 1
Control 3

Certainly, a "COUNTIF" function could be used to provide a total number of
cells having the value of "0" and the total number of cells having the value
"1", but I'd like to be able to create a complex function based on multiple
criteria so I can produce a table like that depicted above.

Thanks in advance for any assistace provided.

Kurt

Use SUMPRODUCT of the form:

=SUMPRODUCT(--(A1:A1000=Site),--(B1:B1000=Group),--(C1:C1000=Condition))

Site,Group,Condition can be cells holding required values

SUMPRODUCT cannot use whole columns i.e. A:A is not allowed and all arrays
must be same size.

HTH
"Kurt" wrote:

I'm using Excel to maintain records for participants in a multisite clinical
trial of therapy groups. I'd like to use a function that filters records
according to specific criteria so that I get a count of the records meeting
these criteria. I have a column in which I have recorded the research site
(Site A=1, Site B=2). Another column records the experimental condition to
which participants have been assigned (Control=0, Treatment=1). Therapy
group has been recorded in another column (Group 1=1, Group 2=2, etc.).
Here's and example of what the data looks like:

ID Site Condition Group
1 1 1 1
2 1 1 1
3 1 0 1
4 1 1 1
5 2 0 1
6 2 0 1
7 2 1 1
8 2 0 1

A table I could create to provide a breakdown of the number of participants
at each site in each group in each therapy group would look something like
this:

Site A
Group 1
Treatment 3
Control 1

Site B
Group 1
Treatment 1
Control 3

Certainly, a "COUNTIF" function could be used to provide a total number of
cells having the value of "0" and the total number of cells having the value
"1", but I'd like to be able to create a complex function based on multiple
criteria so I can produce a table like that depicted above.

Thanks in advance for any assistace provided.

Kurt