Countif function

[Paul10r]

I have a table that looks like this:

DATE DOW Day 1 Day 2 Day 3

8/1/2008 Friday Camacho Trevino Rivera
8/2/2008 Saturday Camacho Trevino Zepeda
8/3/2008 Sunday Tenner Trevino Zepeda
8/4/2008 Monday Tenner Zepeda Zepeda
8/5/2008 Tuesday Tenner Camacho Camacho
8/6/2008 WednesdayTrevino Camacho Camacho
8/7/2008 Thursday Tenner Trevino Zepeda
8/8/2008 Friday Tenner Trevino Zepeda
8/9/2008 Saturday Tenner Trevino Zepeda
8/10/2008 Sunday Camacho Trevino Zepeda
8/11/2008 Monday Tenner Trevino Zepeda
8/12/2008 Tuesday Tenner Trevino Zepeda
8/13/2008 WednesdayCamacho Trevino Zepeda
8/14/2008 Thursday Tenner Trevino Zepeda
8/15/2008 Friday Tenner Trevino Zepeda
8/16/2008 Saturday Tenner Trevino Zepeda
8/17/2008 Sunday Tenner Trevino Zepeda
8/18/2008 Monday Trevino Camacho Camacho
8/19/2008 Tuesday Trevino Camacho Camacho
8/20/2008 WednesdayTrevino Camacho Camacho
8/21/2008 Thursday Trevino Camacho Camacho
8/22/2008 Friday Tenner Zepeda Zepeda
8/23/2008 Saturday Tenner Zepeda Zepeda
8/24/2008 Sunday Tenner Zepeda Zepeda
8/25/2008 Monday Tenner Zepeda Zepeda
8/26/2008 Tuesday Tenner Zepeda Zepeda
8/27/2008 WednesdayTenner Zepeda Zepeda
8/28/2008 Thursday Tenner Zepeda Zepeda
8/29/2008 Friday Rivera Camacho Tenner
8/30/2008 Saturday Camacho Rivera Trevino
8/31/2008 Sunday Camacho Rivera Trevino

I want formulas that will populate the "?" marks (see below) with counts as
to the total number of times each person's name appears within each column
range for Day 1, Day 2, and Day 3:

Camacho Rivera Tenner Trevino Zepeda
Day 1 ? ? ? ? ?

Day 2 ? ? ? ? ?

Day 3 ? ? ? ? ?

Thanks in advance for your suggestions.

 

[Murray]

I have a table that looks like this:

DATE    DOW     Day 1   Day 2   Day 3

8/1/2008        Friday  Camacho Trevino Rivera
8/2/2008        Saturday        Camacho Trevino Zepeda
8/3/2008        Sunday  Tenner  Trevino Zepeda
8/4/2008        Monday  Tenner  Zepeda  Zepeda
8/5/2008        Tuesday Tenner  Camacho Camacho
8/6/2008        WednesdayTrevino        Camacho Camacho
8/7/2008        Thursday        Tenner  Trevino Zepeda
8/8/2008        Friday  Tenner  Trevino Zepeda
8/9/2008        Saturday        Tenner  Trevino Zepeda
8/10/2008       Sunday  Camacho Trevino Zepeda
8/11/2008       Monday  Tenner  Trevino Zepeda
8/12/2008       Tuesday Tenner  Trevino Zepeda
8/13/2008       WednesdayCamacho        Trevino Zepeda
8/14/2008       Thursday        Tenner  Trevino Zepeda
8/15/2008       Friday  Tenner  Trevino Zepeda
8/16/2008       Saturday        Tenner  Trevino Zepeda
8/17/2008       Sunday  Tenner  Trevino Zepeda
8/18/2008       Monday  Trevino Camacho Camacho
8/19/2008       Tuesday Trevino Camacho Camacho
8/20/2008       WednesdayTrevino        Camacho Camacho
8/21/2008       Thursday        Trevino Camacho Camacho
8/22/2008       Friday  Tenner  Zepeda  Zepeda
8/23/2008       Saturday        Tenner  Zepeda  Zepeda
8/24/2008       Sunday  Tenner  Zepeda  Zepeda
8/25/2008       Monday  Tenner  Zepeda  Zepeda
8/26/2008       Tuesday Tenner  Zepeda  Zepeda
8/27/2008       WednesdayTenner Zepeda  Zepeda
8/28/2008       Thursday        Tenner  Zepeda  Zepeda
8/29/2008       Friday  Rivera  Camacho Tenner
8/30/2008       Saturday        Camacho Rivera  Trevino
8/31/2008       Sunday  Camacho Rivera  Trevino

I want formulas that will populate the "?" marks (see below) with counts as
to the total number of times each person's name appears within each column
range for Day 1, Day 2, and Day 3:

        Camacho Rivera  Tenner  Trevino Zepeda
Day 1   ?       ?       ?               ?             ?

Day 2   ?       ?       ?       ?       ?

Day 3   ?       ?       ?       ?       ?

Thanks in advance for your suggestions.

Paul

You can do this using an array formula. Assuming your data (including
the headings) is in A1-E50, and your output table in G1-L4, then the
following formula in H2 will count all "Camacho" for Day 1 (use CTRL-
SHIFT-ENTER to enter the formula - it will appear with braces around
it):

=SUM(IF((C2:C50=H1),1,FALSE))

You will probably find it easier if you arrange your output table the
other way around ie put "Day 1" etc across the top and names down the
side, like so:

Day 1 Day 2 Day3
Camacho
etc

Assuming this output table in G1-J6, then formula in H2 is =SUM(IF((C
$1=H$1)*(C$2:C$28=$G2),1,0)). Again use CRTL-SHIFT-ENTER, then copy
this across and down to fill the table.

More info on array formulas at Chip Pearsons wonderful site.
http://www.cpearson.com/excel/ArrayFormulas.aspx

Regards

Murray

 

[Bob Phillips]

=SUMPRODUCT(--(Sheet!$A$1:$A$100=B$1),--(Sheet1!C$1:C$100=$A2))

copy down and across

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)