Excel: how to sum cells that match a given criteria?

[Starriol]

Hi guys, I need that, let me show you, it will be easier to undertand.

A 1
A 2
A 3 6
B 2
B 5 7
C 1
C 1
C 5
C 3 10

So, basically, I have A LOT of different names in the columns with the letters.
I need Excel to look at column A and sum the values on column B, as long as the value on A has not changed. If it did, it should stop doing the sum, copy the value at C column and start again.

Can this be done with a function or it requires programming a macro?

Anyone can help, whether it is by a function or programming?

Thanks a lot!

 

[Usquabach]

i was trying to figure it out but am not sure what you are really looking for. excel is tough to explain with words - maybe try to post a screenshot of your sheet and it might be easier to understand

but it sounds like you need to use a function in column d like =if(a1="fred",(b1+c1),c1)

and then in column e you need an =if(b1=d1,(c1+d1),d1)

but that's just a guess really since i'm not sure the exact layout of your sheet - and if you are trying to sum b1 but it contains two numbers (i.e. 5 7) you need to use the text to columns command to get each number in a separate cell (use delimited and select space as the delimiter)

 

[arkamw]

Try this. In a cell, drop this formula:

=SUMIF(A1:A31,"=a",B1:B31)

Substitute the first range for the range you want to look at (the column with A and B, etc). Change the stuff between the quotes to match whatever letter you are looking to sum up (no, there is too much, let me sum up!), and change the last range to the total range in the column with the numbers that you are wanting to add.

The only problem with doing it this way is that you are going to need to need a new field and a new formula for each letter, which may be what you are wanting anyway. There are several more ways of going about this but this will probably get you close to what you want.

Hmmm. Looking at your OP again, depending on how many names you have, the above will be pretty kludgy. If you've got a lot of names, the way presented here will take a while. Coding would speed that up a lot.

I think that I'd sort the columns by name, then set up a loop to look at each row and compare it to the previous while keeping a running tally of the total. If the names don't match, spit out the result and the total. This wouldn't be hard but I'm not a coding whiz so it'd take longer that I've currently got.

 

[hl3395]

arkamw is pretty close. Assuming the data starts at the very top left you can use:

=IF(A1=A2,"",SUMIF(A:A,A1,B:B))

That should spit out a value only when the next row has a different name.

 

[arkamw]

arkamw is pretty close. Assuming the data starts at the very top left you can use:

=IF(A1=A2,"",SUMIF(A:A,A1,B:B))

That should spit out a value only when the next row has a different name.

Wow, that's pretty darn spiffy. I've not seen the A:A range selection before. I tend to sledgehammer the problems in Excel (code vs formula). You'd just have to make sure the data is sorted by name.

Thanks!