Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jun 2015
    Posts
    8
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Interrogating data #2

    Hi
    I have received so much help from this forum, thought i would give it another go. Apologies i do not know the technical terms. i have attached a portion of a much larger spread sheet with a massive amount of data. I need to be able to pull out different stats TEST 2.xlsxTEST 2.xlsx

    I need to know: What is the total number of (for example), columns W:AB that have a corresponding number 1 in column I, that has a corresponding number 1 in H - See attached S/S.

    I hope i have explained it so someone can understand
    Thanks
    Tim

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Tim,

    =SUMIF(W2:AB14,1,W2:AB14)

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Jun 2015
    Posts
    8
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Man that was quick!
    Thanks heaps, i have been struggling with this for ages. You live and learn eh. I'll let you know how i go, thanks again.

  4. #4
    New Lounger
    Join Date
    Jun 2015
    Posts
    8
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Man that was quick!
    Thanks heaps, i have been struggling with this for ages. You live and learn eh. I'll let you know how i go, thanks again.

  5. #5
    New Lounger
    Join Date
    Jun 2015
    Posts
    8
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hi HTH
    I tried your formula and get answer = 4, it should be 8, as per attached.
    I have hi-lited the cells i need.
    I think it might be the "1" in =SUMIF(W2:AB14,1,W2:AB14)
    This formula is totaling the number of entries, not the sum of the numbers. I hope you understand that, i am struggling to (a beginner!)

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 Posts
    RG,

    There is a condition in the request that there must be a 1 in columns I and H to be part of the Sum in W:AB. Am I missing something here?

    Timboc,

    You description in your OP (using columns I and H) does not match the description you posted in your worksheet (using columns H and J).

    You could also use Sumproduct to easily do the calculation. Here's both:

    OP description:
    =SUMPRODUCT((W2:AB14)*((I2:I14=1)*(H2:H14=1)))

    Workbook description:
    =SUMPRODUCT((W2:AB14)*((J2:J14=1)*(H2:H14=1)))
    HTH,
    Maud

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Tim,

    Ok I'm lost as to your requirements.

    Do you want to count the cells in W:AB that have a value? Which would be 6
    Or Count the 1's in W:AB? Which would be 4
    Or count the entries in W:AB that have a matching value in I. Which would be 1
    Tim.JPG
    I don't see any to get a value of 8?

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    New Lounger
    Join Date
    Jun 2015
    Posts
    8
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Retired Geek - You are so right, i stuffed up, sorry.
    Maudibe, =SUMPRODUCT((W2:AB14)*((J2:J14=1)*(H2:H14=1))) works a treat, although i just can't understand it
    I will get myself a good book and work through it. (so much a novice)

    Thank you both for being so patient! I really appreciate the help.

Posting Permissions

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