I use an invoice template workbook to run my little business. First 2
columns in the Invoice template is "Item no" and Quantity". I also
created a summary sheet in my work book and in Column "A" entered all
the item no's that I carry in the store.
I am trying to figure out a formula that looks at the item no in my
summary sheet and than go to my invoice template and calculate no of
times that item was sold and place the result in column "B". I would
also like to than have formula in Column "C" that provides total
quantity of that specific items sold.
I experimented with "Countif" function that can atleast tell me how
many times that Item no was sold, but doesn't provide cumulative qty
sold for that item. Also this function taxes Excel and slows the whole
program down. I tried "Vlookup" function but it only reports the first
entry of the specific item that it finds that does not help me either.

I am an Excel newbie so any and all help is much appreciated..

Thanks for your time...

=SUMIF(Invoice!$A$2:$A$100,A2,Invoice!$B$2:$B$100) will give quantities

or

=SUMPRODUCT(--(Invoice!$A$2:$A$100=A2),Invoice!$B$2:$B$100,)

where Invoice is template and A2 is Item in your sumary sheet

HTH

"Amean1" wrote:

I use an invoice template workbook to run my little business. First 2
columns in the Invoice template is "Item no" and Quantity". I also
created a summary sheet in my work book and in Column "A" entered all
the item no's that I carry in the store.
I am trying to figure out a formula that looks at the item no in my
summary sheet and than go to my invoice template and calculate no of
times that item was sold and place the result in column "B". I would
also like to than have formula in Column "C" that provides total
quantity of that specific items sold.
I experimented with "Countif" function that can atleast tell me how
many times that Item no was sold, but doesn't provide cumulative qty
sold for that item. Also this function taxes Excel and slows the whole
program down. I tried "Vlookup" function but it only reports the first
entry of the specific item that it finds that does not help me either.

I am an Excel newbie so any and all help is much appreciated..

Thanks for your time...