Automatic sum function in Excel

Question

I have three columns with 1500 rows in this form:

Name codenb  area

X1   1       12
X2   1       8
X3   2       16
X5   3       2

X6   4       20
X7   5       5
...  ...     ..
Xn 

Anyone have an idea on how can I calculate automatically the sum only of the rows that have the same codenb?

Answer 1

By sum you mean the sum of the area?

You can use SUMIF().

=SUMIF(B:B,1,C:C)

For instance will give you the sum of all the codenb equal to 1.

B:B is the column with the criteria (column codenb);
1 is the codenb that you're summing;
C:C is the column containing the values to sum.

---

If you want to get a table with a summary of all the codenb and the sum of each group of areas, then a PivotTable will be easiest/fastest.

1. Insert a PivotTable on the table in a new sheet;
2. Put (click + drag) column codenb in 'Row Labels' from the right pane;
3. Put (click + drag) column area in 'Values' from the right pane;
4. Make sure that the values are summarised by 'Sum'.

Answer 2

You can use SUMIF.

For example, to sum the rows that have codenb == 1, you would use:

SUMIF(B:B, 1, C:C)

Therefore, you can make a table with the possible codenb values, and substitute the second argument in that formula for it.

Answer 3

A single formula:

=IF(COUNTIF(B:B,B2)>1,SUMIFS(C:C,B:B,B2),"")

might suit.

Answer 4

Use a pivot table. You can quickly sum all areas for each codenb, and add a column "count of codenb" that will show how many rows produced that sum: