# Automatic sum function in Excel

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?

• Could you check your question and see if that's your actual table? It was edited and one line was removed. I didn't see it until I checked back your original question. – Jerry Jun 3 '13 at 18:44

## 4 Answers

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'.

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.

A single formula:

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

might suit.

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:

• Then rearrange the pivot table to include a "count of codenb" column. See my edited answer above. – F106dart Jun 3 '13 at 19:01