Nesting Functions?

[Baxter]

I've tried a variety of ways to accomplish the following:

I have one column (C) with various categories and a second column (D) that
has dates associated with some and others are blank.

B C D
NAME ROLE INTERVIEW
bill reception 14-mar-01
john driver
harry driver 1-apr-01
frank installer 6-jun-01
mary driver
ann reception 4-oct-01

I want to sum the number of interviews I have conducted for each category of
role. Ideas?

 

[T. Valko]

One way...

F1:Fn = unique list of the categories

Enter this formula in G1 and copy down as needed:

=SUMPRODUCT(--(C$1:C$10=F1),--(D$1$10<>""))

 

[Sean Timmons]

easiest would probably be a pivot..

Data > Pivot Table...

Drop ROLE in the Row fields section and Interview in the data fields.

Ensure INTERVIEW is set to count.

Alternately, if you have a list of roles, you can use

=SUMPRODUCT(Sheet1!C2:C100=A2)*(Sheet1!D2100<>""))

Assumes your data is on a tab named Sheet1, your data is in rows 2 through
100 or less, and your new table starts with headers in row 1, with roles
listed in A2 down.