Formula For Adding Variable Number Of Cells Required

[:-)]

How can I get Excel to add up the components of each invoice in a lis
where the total of each invoice is one made up of any number of smalle
sums?

For example, I could have invoice numbers 1 to 3 in Column A and thei
sums in Column B:

Invoice number 1 could have 3 sub entries of 5,6,7 (in cells B1-B3)
Invoice number 2 could have 1 sub entry of 4 (in B4)
Invoice number 3 could have 5 sub entries of 3,4,5,6,7 (in B5-B9)

I would require Column C to have at the last entry of Invoice 1 (C3
the sum of 18; at the entry of Invoice 2 (C4) the sum of 4; and, at th
last entry of Invoice 3 (C9) the total of 25.

Apologies if this is a little(!) complicated but I have tried t
simplify it as much as possible and if anyone can help, I'd be gratefu
as doing the calculations manually for 200 invoices takes hours.

Thank

 

[Jamal]

You can use the Data>>Subtotal to add up column B. But
this will add the totals at the bottom of each sequence.
If you wnat to have the totals in column C then use the
following formula from C2 downward. This assumes that your
data is organised A:A3 are titles and A2 onwarsd are data.


=IF(A4<>A5,SUMIF($A$1:A4,A4,$B$1:B4),"")

Regards

Jamal

 

[:-)]

I'm not sure if I was clear enough before so here's my second attempt a
asking my question:

(I advise you to please open a spreadsheet and put in the numbers a
otherwise its a bit difficult to see what I mean or alternatively chec
the attachment)

Cells in Column A have invoice numbers

Cell A1 = Inv1
Cell A2 = Inv1
Cell A3 = Inv1
Cell A4 = Inv2
Cell A5 = Inv2
Cell A6 = Inv3
Cell A7 = Inv3
Cell A8 = Inv3
Cell A9 = Inv3

Cells in Column B have amounts

Cell B1 = 2
Cell B2 = 5
Cell B3 = 8
Cell B4 = 7
Cell B5 = 9
Cell B6 = 1
Cell B7 = -1
Cell B8 = 3
Cell B9 = 7

I would require in Column C the totals PER INVOICE at the last row o
each invoice. Ie I would need a formula to produce the followin
results:

Cell C1 = <blank>
Cell C2 = <blank>
Cell C3 = 15
Cell C4 = <blank>
Cell C5 = 16
Cell C6 = <blank>
Cell C7 = <blank>
Cell C8 = <blank>
Cell C9 = 10

Hopefully, this is now a bit clearer. I am quite desparate for a
answer so any help would be hugely appreciate

Attachment filename: example.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=59066

 

[Don Guillett]

try this in cell c1 and copy down
=IF(A1=A2,"",SUMIF(A:A,A1,B:B))

 

[:-)]

That's it!!

Thanks a load, Mr Guillett

I LOVE this forum

 

[Don Guillett]

Glad to help. I do too. Call me Don