Bramblerose 09:00 03 Jun 2008
Locked

Each month I need to collate information from a number of invoices. Each invoice has a different number of lines on it.

What I need/want to be able to do is automate this process as much as possible.

I have a complete list of products from which I have created a lookup (with some help!) and this part works fine in that I type in a product code and the description and pack size come up automatically.

What I need to do now is check the amount of each invoice is correct as I am going along.

Someone I spoke to said that if I numbered each entry, so all lines on invoice one would have a number 1 in column A, all lines on invoice two would have a number 2 and so on, then I could add up the total of each invoice somehow and that is where I am stuck. I don't know how to this!

Thanks

VoG II 09:09 03 Jun 2008

Perhaps you mean something like

=SUMIF(A1:A10,1,B1:B10)

which will sum the values in column B if column A contains 1.

Bramblerose 09:17 03 Jun 2008

I kind of knew it would be something simple.

I have used that and it works and I can tweak it to work if A contains 2,3,4 etc

Thanks!

xania 09:22 03 Jun 2008

SUMIF will solve this for you i.e

SUMIF(A1-A100)=1
SUMIF(A1-A100)=2

with the alternative formula going into different cells. But you would need to know the maximum number of invoices

Or you can set up a macro.

Sub suminvoices()

set n=1

loop

SUMIF(A1-A100)=1

if (some condition based on the fact you have come to the end of the list) goto endif

set n=n+1

goto loop

endif

endsub

Sorry, but I can't seem to get the formating right on this system, but I hope you get the idea

NB You can use COUNTIF inthe same way to count the number of items in the invoice.

xania 09:24 03 Jun 2008

Sorry VOG - you're quicker than me, and I lost mine half way through!!

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

Alice Saey's mesmerising animation for Dutch singer Mark Lotterman