Microsoft Office Forums - View Single Post - [Solved] Formula to delete but maintain counts after the maximum has been reached

wheddingsjr · 01-03-2018, 01:22 PM

Hi all

I have a spreadsheet that contains four columns of data (see attached). Column ‘C’ is a formula that tallies the numbers in column ’B’. The accumulation is based on the acct# in column ‘A’. Each time the Acct# changes the count starts again.
Column ‘E’ is a formula that adds column ‘D’ & ’B’. The formula that I don’t have but need is for column ‘D’. Is there a formula that can be written that looks at column ‘C’ to determine if that number exceeds “12” and if so by how much? (see sample in column D). So, at the end of the day, column ‘C’ will have the tallied total, column ‘D’ will have the number of units over by line, and column ‘E’ will have the maximum of 12 units. Obviously the two columns added would equal column ‘C’. I have been manually going through 30k+ lines manually doing this and if there is a formula that could help me that would be greatly appreciated.

Thanks