-1

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?

1
  • 1
    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
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'.
0
0

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.

0
0

A single formula:

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

might suit.

0
0

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:

enter image description here

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.