Sum bank deposits records

New Contributor

Please see the attached sheet.

I want to add the amounts in column M (total) for each deposit number in Column B. 

For example, Dep #1041 (B5 and B6), the total for this deposit number (197.1+49.29=246.39). I only want ONE amount for each deposit number. I want to be able to drag this formula for all other deposit numbers. 

(I did =SUMIF(B5:B16,B5,M5:M16), but it gives me 49.29 for row 6. I want only once 246.39 for both rows). 

2 Replies

@shlomo08701 

The easiest way to do this is to create a pivot table, with Dep # in the Rows area and Total in the Values area.

 

If you want formulas to the right of the current data, enter the following formula in - for example - Q5:

 

=IF(COUNTIF(B$5:B5,B5)=1,SUMIF(B$5:B$16,B5,M$5:M$16),"")

 

Fill down to row 16.

 

See the attached version.

@Hans Vogelaar OMG, that's magic!! Thank you so much!!