Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post
    I have a simple sheet, say 10x10 cells. First column contains one of four values A, B, C or D. The other 10x9 cells contain integers.
    e.g.

    A 1 2 3 4 5 1 2 3 4
    B 1 2 3 4 5 1 2 3 4
    C 1 2 3 4 5 1 2 3 4
    D 1 2 3 4 5 1 2 3 4
    A 1 2 3 4 5 1 2 3 4
    B 1 2 3 4 5 1 2 3 4
    C 1 2 3 4 5 1 2 3 4
    D 1 2 3 4 5 1 2 3 4

    I want to be able to add up all cells from all rows (columns 2 to 10) where there is a "A" in the first column of that row, so in this case it would add up

    SUM(row 1 integers) + SUM(row 5 integers) i.e. ( 1+2+3+4+5+1+2+3+4) + (1+2+3+4+5+1+2+3+4) since row 1 and row 5 have an "A" in the first column.

    So it's kind of like a SUMIF but SUMIF just returns the SUM of a single column where the rows correspond to an "A".

    I want to be able to do this calculation in VBA without having to do a loop - I want to use a formula if I can.

    Any ideas?

  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
    If you want a formula: Why not create a column which sums up all the columns in a row, then just use the sumif on that single column?

    If you are going to do it in VBA, I don't see why you have an issue with looping...

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post
    [quote name='sdckapr' post='776273' date='21-May-2009 16:49']If you want a formula: Why not create a column which sums up all the columns in a row, then just use the sumif on that single column?

    If you are going to do it in VBA, I don't see why you have an issue with looping...

    Steve[/quote]

    Hi thanks for the reply. I can't SUMIF on the single column because I would first have to sum a particular number of columns (not all of them) depending on another formula.. so too complicated.

    I don't have an issue with looping - I just figured that for a "large" spreadsheet, using a built-in function would be faster than looping through many columns doing SUMIFs on each of them.

    In the meantime I have done it with looping, but I haven't tried a large sheet yet.

  4. #4
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    [quote name='dom_donald' post='776288' date='21-May-2009 17:55']I can't SUMIF on the single column because I would first have to sum a particular number of columns (not all of them) depending on another formula.. so too complicated.[/quote]
    Surely you can sum each ROW and then use SUMIF to add the sums for the rows IF the correct letter is in the first column.

  5. #5
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post
    [quote name='StuartR' post='776293' date='21-May-2009 18:36']Surely you can sum each ROW and then use SUMIF to add the sums for the rows IF the correct letter is in the first column.[/quote]
    Nope, sorry.. because the columns are months/years and run continuously from the start (of the project) to the end. I need to do calculations for a given year. I can't sum a row because the row spans several years.

  6. #6
    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
    You could in the intermediate column, have the formula choose (however it needs to be done) the appropriate columns in that row to add together, THEN use SUMIF on this intermediate column...

    Steve

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='dom_donald' post='776306' date='21-May-2009 15:12']Nope, sorry.. because the columns are months/years and run continuously from the start (of the project) to the end. I need to do calculations for a given year. I can't sum a row because the row spans several years.[/quote]

    Have you tried using Conditional Sum Wizard?

    [attachment=83888:SP32_200...1_163010.jpg][attachment=83889:SP32_200...1_162951.
    jpg]
    Attached Images Attached Images
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  8. #8
    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
    You could use:
    =SUMPRODUCT((A1:A8="A")*B1:J8)
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post
    [quote name='rory' post='776408' date='22-May-2009 12:51']You could use:
    =SUMPRODUCT((A1:A8="A")*B1:J8)[/quote]

    hmm that should do the trick

Posting Permissions

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