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  
 
 [ 3 post ] 

 Relevant Pages 

1. Summing using formulas already in cells, not cell references, or values in cells

2. need cell formulas to display results not the formula

3. Need formula for Sum of single Cell

4. Need to replace cell formula with cell formula

5. Summing adjacent cells: another cell added to formula!

6. Populating a formula in a large spreadsheet

7. SUM, If a cell is blank do not SUM the Range

8. To Sum or not sum w/ blank cells

9. Word formula SUM(ABOVE) won't include Excel Spreadsheet calc

10. Need Advice on importing a LARGE excel spreadsheet into Access 2000


 
Powered by phpBB® Forum Software © phpBB Group