Results 1 to 3 of 3

Thread: CountIf?

  1. #1
    2 Star Lounger
    Join Date
    Dec 2002
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts
    My headers are
    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?
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    Quote Originally Posted by buckshot View Post
    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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