1. 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. Tim,

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

HTH

3. 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. 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. 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. 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. 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?

8. 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
•