formula needed to search account numbers, sum totals and compare

I am having trouble with what I thought would be a relatively simple calculation. I have an existing spreadsheet that identifies account numbers, separates by product, lists $ past due, and also lists $ I owe them. I am trying to figure out if the past due $ they owe me is greater than the $ I need to refund. The problem is, the refund information I have is broken down by product type, and each line of data has the total amount past due (all products).

I’m attaching an extremely simplified spreadsheet as a reference example. Basically, for each group of account numbers I need a formula that will search column A for identical account numbers then total the balance in the corresponding cells in column D and then compare that to a single cell in column C. For example…Account #100 is on lines 2-5. I need to add D2:D5, then take that figure and compare to C2 with an output in column e identifying if the sum of cells in column D was larger, or the single cell for column C.

Any help is greatly appreciated...thanks.

=SUMIF($A$2:$A$14,A2,$D$2:$D$14) gets the sum of all column D where column A is same as current row's A value.

you can add to the formula:

e.g

=SUMIF($A$2:$A$14,A2,$D$2:$D$14)-C2 will give the difference with column C

or

=SUMIF($A$2:$A$14,A2,$D$2:$D$14)>C2 will give TRUE if sum is larger than C value or FALSE if not.

copy formula down.

One way is use Pivot Table, & use a calculated Field as =MAX(0,'Past Due'-'Refund $ Owed')

See the attached.