I am trying to write a formula that would say count number of cells with
traumatic and overuse injuries by body part based on sample data below

A B C
1 Sam head trauma
2 Joe neck overuse
3 Mary shoulder overuse
4 Sam shoulder trauma
5 Henry head overuse
6 Carlos neck overuse

Results should look like this

A B C
1 Trauma Overuse
2 Head 1 1
3 neck 0 2
4 Shld 1 1

The following formulas do not work: =COUNTIF(b1:b6,A2)+COUNTIF(c1:c6,b1)
COUNTIF(b1:b6,A2)*ANDCOUNTIF(c1:c6,b1)

Hi!

With your table on Sheet1 and the results table on Sheet2....

In Sheet2 B2 enter this formula and copy across to C2 then down to C4:

=SUMPRODUCT(--(Sheet1!$B$1:$B$6=$A2),--(Sheet1!$C$1:$C$6=B$1))

Biff

"Sojo" wrote in message
...
I am trying to write a formula that would say count number of cells with
traumatic and overuse injuries by body part based on sample data below

A B C
1 Sam head trauma
2 Joe neck overuse
3 Mary shoulder overuse
4 Sam shoulder trauma
5 Henry head overuse
6 Carlos neck overuse

Results should look like this

A B C
1 Trauma Overuse
2 Head 1 1
3 neck 0 2
4 Shld 1 1

The following formulas do not work: =COUNTIF(b1:b6,A2)+COUNTIF(c1:c6,b1)
COUNTIF(b1:b6,A2)*ANDCOUNTIF(c1:c6,b1)

One other thing......

Make sure that "shoulder" in the table on Sheet1 and "shld" in the results
table are the same. Use either "shoulder" or "shld" in both locations.

Biff

"Biff" wrote in message
...
Hi!

With your table on Sheet1 and the results table on Sheet2....

In Sheet2 B2 enter this formula and copy across to C2 then down to C4:

=SUMPRODUCT(--(Sheet1!$B$1:$B$6=$A2),--(Sheet1!$C$1:$C$6=B$1))

Biff

"Sojo" wrote in message
...
I am trying to write a formula that would say count number of cells with
traumatic and overuse injuries by body part based on sample data below

A B C
1 Sam head trauma
2 Joe neck overuse
3 Mary shoulder overuse
4 Sam shoulder trauma
5 Henry head overuse
6 Carlos neck overuse

Results should look like this

A B C
1 Trauma Overuse
2 Head 1 1
3 neck 0 2
4 Shld 1 1

The following formulas do not work: =COUNTIF(b1:b6,A2)+COUNTIF(c1:c6,b1)
COUNTIF(b1:b6,A2)*ANDCOUNTIF(c1:c6,b1)