CODEWITHSUNDEEP
excelif-statementmergesumconditional-statements
Deepesh
Deepesh

Reputation: 840

Adding rows that match a criteria in another column in Excel

This is a sample data

Polling_Booth   INC SAD BSP PS_NO
1               89  47  2   1
2               97  339 6   1
3              251  485 8   1
4              356  355 25  2
5              290  333 9   2
6              144  143 4   3
7              327  196 1   4
8              370  235 1   5

And this is what I'm trying to achieve

Polling_Booth      INC  SAD BSP  PS_NO   OP_INC OP_SAD OP_BSP
    1               89  47  2    1        
    2               97  339 6    1
    3              251  485 8    1        437   871    16 
    4              356  355 25   2
    5              290  333 9    2        646   688    34
    6              144  143 4    3        144    143    4
    7              327  196 1    4        327    196    1
    8              370  235 1    5        370    235    1

This is achieved adding up rows which has the same PS_NO, This is what I have tried

=if(E2=E3,sum(B2,B3),0)    #same for all the rows

Any help would be much appreciated..Thanks

Upvotes: 0

Views: 32

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34230

You could get it to look like your table by adding another condition to check if it's the last occurrence of the PS_No in column E and setting the result to an empty string if not

=IF(COUNTIF($E$2:$E2,$E2)=COUNTIF($E$2:$E$10,$E2),SUMIF($E$2:$E$10,$E2,B$2:B$10),"")

enter image description here

If the data is sorted by PS_No, you can do it more easily by

=IF($E3<>$E2,SUMIF($E$2:$E$10,$E2,B$2:B$10),"")

which I think is what you were trying in your question

Upvotes: 1

Related Questions