Need Formula to sum cells in large spreadsheet that are not in sequential order
Author Message
Need Formula to sum cells in large spreadsheet that are not in sequential order

I have a spreadsheet that lists invoice amounts paid (line-by-line) against
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             e1
f1
invoice #       invoice line.       amount         po #         po line
po line amount

1134            1                       50                53416       3
200

1135            1                       50               53416       3
200

1136            2                      50                53416        3
200

1137            5                      50                53416        3
200

1157            1                      45                53416        1
90

1159            1                      45                53416        1
90

total           290                    <>
980

I need a formula that will only sum the line amount for each purchase order
line once.

290 for invoices  = 290 for total po amount.

Thanks

Wed, 18 Jun 1902 08:00:00 GMT
Need Formula to sum cells in large spreadsheet that are not in sequential order
Hi,

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:

=IF(B1=B2,"",SUMIF(B:B,B1,C:C))

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.

HTH

Peter Beach

Quote:
> I have a spreadsheet that lists invoice amounts paid (line-by-line)
against
> 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
e1
> f1
> invoice #       invoice line.       amount         po #         po line
> po line amount

> 1134            1                       50                53416       3
> 200

> 1135            1                       50               53416       3
> 200

> 1136            2                      50                53416        3
> 200

> 1137            5                      50                53416        3
> 200

> 1157            1                      45                53416        1
> 90

> 1159            1                      45                53416        1
> 90

>                          total           290                    <>
> 980

> I need a formula that will only sum the line amount for each purchase
order
> line once.

> 290 for invoices  = 290 for total po amount.

> Thanks

Wed, 18 Jun 1902 08:00:00 GMT
Need Formula to sum cells in large spreadsheet that are not in sequential order
Auth;

Your use of the term "line" has me confused, but I think you need to use a
pivot table. It will group all the PO numbers (or any column you want),
adding the amounts (or any other column) for each group it finds. The data
rows need not be in any particular order.

--
Regards from {*filter*}ia Beach,

EarlK

-------------------------------------------------------------

Quote:
> I have a spreadsheet that lists invoice amounts paid (line-by-line)
against
> 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
e1
> f1
> invoice #       invoice line.       amount         po #         po line
> po line amount

> 1134            1                       50                53416       3
> 200

> 1135            1                       50               53416       3
> 200

> 1136            2                      50                53416        3
> 200

> 1137            5                      50                53416        3
> 200

> 1157            1                      45                53416        1
> 90

> 1159            1                      45                53416        1
> 90

>                          total           290                    <>
> 980

> I need a formula that will only sum the line amount for each purchase
order
> line once.

> 290 for invoices  = 290 for total po amount.

> Thanks

Wed, 18 Jun 1902 08:00:00 GMT

 Page 1 of 1 [ 3 post ]

Relevant Pages