We help IT Professionals succeed at work.

How sum numbers in 2 columns where the values in column A are the same

SteveL13
SteveL13 asked
on
In my Excel file I have 4 columns.  In column "A" I have a text field.  The values in this text field may repeat 1 or more times in the rows but the file is sorted by this row so they are all together.   Then, in a 5th column I want to sum the numeric values of the data in column "C" where the text string is the same in column A.  Then I also want to do this in yet another new column, summing the values in column D where the text string is the same in column A.

Please see my sample file to see what I'm trying to do.

 Sample.xlsx
Comment
Watch Question

BRONZE EXPERT
Most Valuable Expert 2013

Commented:
Hello Steve,

Try this formula in E2 copied to F2 then down both columns

=SUMIF($A:$A,$A2,C:C)

see attached

regards, barry
27430459.xlsx
SILVER EXPERT
Most Valuable Expert 2011
Awarded 2010

Commented:
Hello,

=SUMIF(A:A,A2,C:C)

this should do it.

cheers, teylyn
Barry TiceBusiness Analyst
BRONZE EXPERT

Commented:
I'm afraid I'm not clear on what you're looking for, SteveL13.

Are you wanting to add the 40 to 37058 to get 37098 in column G?
Are you wanting the 40 in E3 to add to the 37058 in F2 to make 37098 in F3 because A3 = A2?
What do columns B, C and D have to do with things? Area they to be ignored?
HainKurtSr. System Analyst
BRONZE EXPERT

Commented:
here it is

I gave names to ranges and used those names in formula

E2 : =SUMIF(CUSTOMER,A8,SUMOFHOUR)

then copy down
Sample.xlsx
HainKurtSr. System Analyst
BRONZE EXPERT

Commented:
oops,

E2 : =SUMIF(CUSTOMER,A2,SUMOFHOUR)

and copy down

Author

Commented:
This works when I copy down a few rows.  But my file has 4500 rows and it is taking forever.
BRONZE EXPERT
Most Valuable Expert 2013

Commented:
Are the names sorted in blocks like your example? If so you could use a formula that would sum for the first name in each block only - that would make it quicker, i.e. for E2 copied down

=IF(A2<>A1,SUMIF($A:$A,$A2,C:C),"")

and similar for F2

SUMIF is normally quick, though, are you sure it's not something else that is slowing you down?

regards, barry

Author

Commented:
That won't work becuase I have to have the sum in each cell down the row for the values in A
BRONZE EXPERT
Most Valuable Expert 2013

Commented:
Are you saying it's "taking forever" to calculate or to copy the formulas down?

If it's the former then I don't know what to suggest. SUMIF is about as quick as you will get. I put some random values in 10,000 rows and then used those SUMIF formulas in both columns (approx 20000 formulas). It took about 3 or 4 seconds to calculate, even with RAND() calculations in some cells that would slow it down further.

Do you have other formulas in the sheet that might be slowing it down?

regards, barry

Author

Commented:
Is taking forever to copy the cells down.  And there are no other formulas in the file.
BRONZE EXPERT
Most Valuable Expert 2013
Commented:
OK, that's more understandable,

f you have continuous data in the adjacent column you can fill down with a double click. Delete existing formulas then put the first formula back in E2. Select E2 and put the cursor on the bottom right corner of the cell until you see a black "+" - that's the "fill handle" - double click and the formula will populate the whole column as far as you have continuous data in the next column (column C). Repeat for column F

If there isn't continuous data in the adjacent column you can use this method

Put formula in E2 and select that cell. In the box above A1 type the whole range for the formula (e.g. E2:E5000) and press ENTER. This selects the whole range. Now press CTRL+D - that fills the formula

regards, barry

Explore More ContentExplore courses, solutions, and other research materials related to this topic.