A1=Year Exp
B1= # of People Which is a formula -Formula (=COUNTIF(\$G\$18:\$G\$10009,A2)
C1= Type F
D1 = Type H
C1 = Type S
A2:A12 contains Exp Years from 1999 to 2010

F17 G17
Type Year
F Exp 1999
F Exp 1999
F EXP 2000
F EXP 2000
F Exp 2002
S Exp 2002
F Exp 2002
S Exp 2003
F Exp 2003
S Exp 2005
S Exp 2005
H Exp 2006
H Exp 2007
S Exp 2008
S Exp 2009

I would like to know how many Type F,H, and S there for each Exp year?

2. In C2:
=SUMPRODUCT((\$F\$18:\$F\$32=MID(C\$1,FIND(" ",C\$1)+1,5))*(\$G\$18:\$G\$32=\$A2))
Copy down and to the right (or to the right and down)

3. Originally Posted by buckshot
I would like to know how many Type F,H, and S there for each Exp year?
Sumifs on multiple criteria before Excel 2007 require some form of array formula. In Cell C2 enter:

=SUMPRODUCT((\$F\$18:\$F\$32=RIGHT(C\$1,1))*(\$G\$18:\$G\$3 2=\$A2)*1)

and copy down and right. In Excel 2007, use the =SUMIFS() form.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•