countif -- mulipl choices

[grizzly6969]

I have on sheet 1 john -- sick
bill --- sick
john --- sick
ted ----- sick
john --- broken leg
is it possible to show on sheet 2 john - sick = 2
john - broken leg = 1
bill - sick = 1
etc. etc.

 

[GSnyder]

Assuming that A1:A10 contains names and B1:B10 contains some sort of
ailments, (with A1 being the header NAME and B1 being the header AILMENT)
you can easily summarize using a pivot table. Also assuming you're using
Excel 2007, perform the following steps:

1. Select any cell within A1:B10
2. Choose Insert->Pivottable.
3. Select OK to create the pivottable
4. Check both Name and Ailment in the field list box.
5. Drag Ailment down to the Values box in the lower right of the field list.

This will give you a pivot table showing each person, their ailments, and
the count of ailments.

From there, you can play around with the pivot and add formatting, remove or
add totals, etc.

Happy calculating!

If you like this answer, please click ''Yes.''

 

[Shane Devenshire]

Hi,

here is a formula approach:

=SUMPRODUCT(--(A1:A10="John"),--(B1:B10="sick"))

or put the name in a cell, say D1 and sick in E1 and use

=SUMPRODUCT(--(A$1:A$10=D1),--(B$1:B$10=E1))

In 2007 you can use

=COUNTIFS(A$1:A$10,D1,B$1:B$10=E1)

 

[Bob Phillips]

In 2007 you can use

=COUNTIFS(A$1:A$10,D1,B$1:B$10=E1)

I think that you mean

=COUNTIFS(A$1:A$10,D1,B$1:B$10,E1)



__________ Information from ESET Smart Security, version of virus signature database 3832 (20090206) __________

The message was checked by ESET Smart Security.

http://www.eset.com