1. 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. 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. [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. [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. [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. 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. [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]

8. You could use:
=SUMPRODUCT((A1:A8="A")*B1:J8)

9. [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
•