Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sum cells based on a condition

    Hi,

    I am trying to sum a series of cells that match a certain condition horizontally

    eg

    A1 = 2
    B1 = "Y"
    C1 = 3
    D1 ="Y"
    E1 = 4
    F1 = ""
    G1 = 1
    H1 = "Y"

    So AA1= the total of A1, C1, E1 and G1 if B1, D1, F1 and H1 = "Y"

    I also then need the reverse to that if not = "Y".

    I can do this with a complex if statement but the range could be quite large, is there a SUMIF formula that would do this, I have looked at SUMIF, SUMIFS and SUMPRODUCT which do not quite work.

    Any advise.

    Regards

    Mike

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Try this:
    =SUMPRODUCT(--(B1:H1="Y"),A1:G1)

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    =SUMIF(B1:H1,"Y",A1:G1)
    and:
    =SUMIF(B1:H1,"<>Y",A1:G1)
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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