Need Formula to sum cells in large spreadsheet that are not in sequential order
Check out the SumIf spreadsheet function. It allows a conditional
summation, which it sounds as if it is what you want. Taking a simple
example with invoice numbers running down col A, P.O. numbers down Col B and
P.O. amounts down col C you could try a formula in Col D like:
This will sum all invoices for a particular P.O. when there is a change in
purchase order number, and produce blanks for other invoice lines.
> I have a spreadsheet that lists invoice amounts paid (line-by-line)
> the purchase order line. I may have 4 invoices posted against one purchase
> order line. The invoice amounts sum up to the total of the line, once they
> are all posted, however, the same line number with its "total line amount"
> is listed 4 times, once for each invoice. I need a formula that will take
> only one line amount per purchase order and sum it so that the total
> purchase order amount will equal the sum of all invoices against the
> purchase order lines. Example:
> a1 b1 c1 d1
> invoice # invoice line. amount po # po line
> po line amount
> 1134 1 50 53416 3
> 1135 1 50 53416 3
> 1136 2 50 53416 3
> 1137 5 50 53416 3
> 1157 1 45 53416 1
> 1159 1 45 53416 1
> total 290 <>
> I need a formula that will only sum the line amount for each purchase
> line once.
> 290 for invoices = 290 for total po amount.