Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Confoederatio Helvetica
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Sum (2000 SP3)

    I've done some searching and found a number of similar posts - but not one that matches what I'm looking for.

    I've got over 900 rows x 10 columns of data. I want to sum values in one column based on matching information in another column. The matching data could be 1 or more (as many as 12). In the table below the first two columns is the data I have and I want to calculate the value in the third column. i.e. AA 100 + 200 = 300, AB 110 = 110, AC 150 + 95 + 100 = 345, AD 200 = 200. I've tried IFSUM, but that doesn't seem to work. Any other ideas? <img src=/S/please.gif border=0 alt=please width=31 height=23>

    <table border=1><td>AA</td><td>100</td><td>AA</td><td>200</td><td>300</td></tr><td>AB</td><td>110</td><td>110</td></tr><td>AC</td><td>150</td></tr><td>AC</td><td>95</td></tr><td>AC</td><td>100</td><td>345</td></tr><td>AD</td><td>200</td><td>200</td></tr></table>

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional Sum (2000 SP3)

    In C1:
    =IF(COUNTIF(A$1:A1,A1)<>COUNTIF(A:A,A1), "",SUMIF(A:A,A1,B:)

    Copy down the column...

    Steve

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Confoederatio Helvetica
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Sum (2000 SP3)

    Steve
    Thanks - the conditional data is in column A but the numbers I need summing are in column E so I changed your formula to

    =IF(COUNTIF(A$1:A1,A1)<>COUNTIF(A:A,A1), "",SUMIF(A:A,A1,E:E))

    and copied down the column. However the results are mostly 0 (zero) I do get some results but they don't appear to be related to the values in E.

    What am I missing?

  4. #4
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional Sum (2000 SP3)

    Assume your range of data, "AA" started at A2

    C2, enter the formula and copied down :

    =IF(A2<>A3,SUMIF(A:A,A2,B:,"")

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional Sum (2000 SP3)

    Are the values truly numbers or are they text that just look like numbers? Remove any cell alignment, numbers align to right and text to the left...

    Otherwise, could you post an example file with the problem?

    Steve

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Confoederatio Helvetica
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Sum (2000 SP3)

    Here's an example

  7. #7
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Confoederatio Helvetica
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Sum (2000 SP3)

    Arghh!!!! You are right - I forgot to Change A1 to A8 - works perfectly now!!!

    Thanks to both Steve and you!!!!

Posting Permissions

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