Summing by Label in Excel / Open O. Calc

[Fifty]

hi! So I have a data sheet that is formatted as follows:

x    1
y    0
x    1
y    0
z    1
x    0
y    1
z    0
z    1
y    0
y    1
z    0
x    1
x    1

What I want is data that will sum the stuff in the 2nd column according to its label in the first column. In this example, it would be something like:

x: 4
y: 2
z: 2

How do I do this? It could be in the form of a numerical sum, or even a graph if that is somehow easier.

 

[Bill3000]

It's a bit wieldy, but...
Assume the first row is 2, columns are A and B. Make a third column C. Type in for C2:

=IF(A2="x",B2,0)

This give the value of B2 in the column if A2 is equal to x, and 0 otherwise. Repeat this for C3, C4, etc, and sum all of them to get the sum for all x's. Repeat this for a column D (with =IF(A2="y",B2,0)) and E for y and z.

 

[Fifty]

It's a bit wieldy, but...
Assume the first row is 2, columns are A and B. Make a third column C. Type in for C2:

=IF(A2="x",B2,0)

This give the value of B2 in the column if A2 is equal to x, and 0 otherwise. Repeat this for C3, C4, etc, and sum all of them to get the sum for all x's. Repeat this for a column D (with =IF(A2="y",B2,0)) and E for y and z.

This won't work. My example was just an example of the format I wanted... my actual data consists of 1000 rows and 34 total labels that need to be summed.

 

[warpus]

Sort by columns A,B, add

 

[FeelGood]

in excel, you can also use - =sumif(label range,"criteria or label", sum range)

eg: =SUMIF(AB23:AB103,"routine",Y23:Y103)

 

[El Koeno]

Array formulas might work, even though they're more resource intensive and unwieldy than SUMIF.

DBSUM and pivot tables might also be an idea, but I've never really used those.

 

[Mise]

Sumif is your best bet.

Put all your labels in a column, e.g. column E:
......E
1.....x
2.....y
3.....z
(etc)

Now in column F, type =sumif(A:A,E1,B:B):
.....E......F
1...x......=sumif(A:A,E1,B:B)

then drag that formula down for all 34 labels.

 

[ainwood]

=countif() might also be of use, if its a count that you actually want.

 

[Conroe]

You might also check out subtotals. The list should be sorted first, though. It is on the data menu IIRC ...

 

[Furiey]

Another vote for sumif. A really usefull function to sum for different criteria without having to sort or format the data in the original list.