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


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:




Fill down to row 16.


See the attached version.

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