counting like items on one sheet, showing totals on another sheet
 

Sorry if this has been answered, I looked at 342 different questions, did not
find one like mine.

Within a workbook, I have 6 sheets. I have an inventory sheet listing all
product by item number and description, ex: "80036" in a1, "36 inch box" in
b1, "80046" in a2, "46 inch box" in b2, etc.. A total of 1200 lines of
different items and descriptions. Then I have 4 quote sheets. Some rows for
each sheet use "VLOOKUP", so if the item number from the inventory sheet is
typed in appropriate col., description from inventory sheet is returned in
next col.. OK, next I have a sheet that is my picking ticket. On this sheet
I want a total by row of each item listed on the 4 quote sheets, so it shows
how many of the same item were listed on the 4 quote sheets. So if "80036"
is on quote sheet one with a value of 4, and on quote sheet two with a value
of 3, my picking tix would show that item number and description with a value
of 7 in the appropriate col.. Not all items will always be listed on quote
sheets. How do I set up the rows on picking tix to look for an item number
on the four quote sheets,and if not found, go to the next item number, once
an
item number is found, sum the total number listed for the found item number,
then on the next row, do the same thing without duplicating what has already
been found, until all 1200 items have been searched for? I think I am asking
too much..........TIA for any and all help.

counting like items on one sheet, showing totals on another sheet
 

Assuming you have all 1200 items listed on the PICKING Ticket sheet with the
item numbers in column A then
(assume quote sheets have item number in column B and quantities in D with
sheet names qsheet1, qsheet2, qsheet3, qsheet4:
In F2 of the picking ticket for example.
=countif(Qsheet1!B:B,A2,Qsheet1!D:D)+countif(Qshee t2!B:B,A2,Qsheet2!D:D)+countif(Qsheet3!B:B,A2,Qshe et3!D:D)+countif(Qsheet4!B:B,A2,Qsheet4!D:D)


then drag fill down the column.

when done and the numbers are returned, if you want to make them permanent,
select column F, do edit=Copy, then Edit=Paste special and select values to
overwrite the formulas with the values they display.

--
Regards,
Tom Ogilvy

"RPW" wrote:

Sorry if this has been answered, I looked at 342 different questions, did not
find one like mine.

Within a workbook, I have 6 sheets. I have an inventory sheet listing all
product by item number and description, ex: "80036" in a1, "36 inch box" in
b1, "80046" in a2, "46 inch box" in b2, etc.. A total of 1200 lines of
different items and descriptions. Then I have 4 quote sheets. Some rows for
each sheet use "VLOOKUP", so if the item number from the inventory sheet is
typed in appropriate col., description from inventory sheet is returned in
next col.. OK, next I have a sheet that is my picking ticket. On this sheet
I want a total by row of each item listed on the 4 quote sheets, so it shows
how many of the same item were listed on the 4 quote sheets. So if "80036"
is on quote sheet one with a value of 4, and on quote sheet two with a value
of 3, my picking tix would show that item number and description with a value
of 7 in the appropriate col.. Not all items will always be listed on quote
sheets. How do I set up the rows on picking tix to look for an item number
on the four quote sheets,and if not found, go to the next item number, once
an
item number is found, sum the total number listed for the found item number,
then on the next row, do the same thing without duplicating what has already
been found, until all 1200 items have been searched for? I think I am asking
too much..........TIA for any and all help.

counting like items on one sheet, showing totals on another sh
 

Ok, but is that going to give me a sum total for an item, or just a total
count of how many times that item was listed on the quote sheets? Thank you
for your help.

"Tom Ogilvy" wrote:

Assuming you have all 1200 items listed on the PICKING Ticket sheet with the
item numbers in column A then
(assume quote sheets have item number in column B and quantities in D with
sheet names qsheet1, qsheet2, qsheet3, qsheet4:
In F2 of the picking ticket for example.
=countif(Qsheet1!B:B,A2,Qsheet1!D:D)+countif(Qshee t2!B:B,A2,Qsheet2!D:D)+countif(Qsheet3!B:B,A2,Qshe et3!D:D)+countif(Qsheet4!B:B,A2,Qsheet4!D:D)


then drag fill down the column.

when done and the numbers are returned, if you want to make them permanent,
select column F, do edit=Copy, then Edit=Paste special and select values to
overwrite the formulas with the values they display.

--
Regards,
Tom Ogilvy

"RPW" wrote:

Sorry if this has been answered, I looked at 342 different questions, did not
find one like mine.

Within a workbook, I have 6 sheets. I have an inventory sheet listing all
product by item number and description, ex: "80036" in a1, "36 inch box" in
b1, "80046" in a2, "46 inch box" in b2, etc.. A total of 1200 lines of
different items and descriptions. Then I have 4 quote sheets. Some rows for
each sheet use "VLOOKUP", so if the item number from the inventory sheet is
typed in appropriate col., description from inventory sheet is returned in
next col.. OK, next I have a sheet that is my picking ticket. On this sheet
I want a total by row of each item listed on the 4 quote sheets, so it shows
how many of the same item were listed on the 4 quote sheets. So if "80036"
is on quote sheet one with a value of 4, and on quote sheet two with a value
of 3, my picking tix would show that item number and description with a value
of 7 in the appropriate col.. Not all items will always be listed on quote
sheets. How do I set up the rows on picking tix to look for an item number
on the four quote sheets,and if not found, go to the next item number, once
an
item number is found, sum the total number listed for the found item number,
then on the next row, do the same thing without duplicating what has already
been found, until all 1200 items have been searched for? I think I am asking
too much..........TIA for any and all help.

counting like items on one sheet, showing totals on another sh
 

Also, if some of the totals (or rows) are zero, when I print out the picking
ticket report, how do I not print any 'zero total' items? Thanks.

"RPW" wrote:

Ok, but is that going to give me a sum total for an item, or just a total
count of how many times that item was listed on the quote sheets? Thank you
for your help.

"Tom Ogilvy" wrote:

Assuming you have all 1200 items listed on the PICKING Ticket sheet with the
item numbers in column A then
(assume quote sheets have item number in column B and quantities in D with
sheet names qsheet1, qsheet2, qsheet3, qsheet4:
In F2 of the picking ticket for example.
=countif(Qsheet1!B:B,A2,Qsheet1!D:D)+countif(Qshee t2!B:B,A2,Qsheet2!D:D)+countif(Qsheet3!B:B,A2,Qshe et3!D:D)+countif(Qsheet4!B:B,A2,Qsheet4!D:D)


then drag fill down the column.

when done and the numbers are returned, if you want to make them permanent,
select column F, do edit=Copy, then Edit=Paste special and select values to
overwrite the formulas with the values they display.

--
Regards,
Tom Ogilvy

"RPW" wrote:

Sorry if this has been answered, I looked at 342 different questions, did not
find one like mine.

Within a workbook, I have 6 sheets. I have an inventory sheet listing all
product by item number and description, ex: "80036" in a1, "36 inch box" in
b1, "80046" in a2, "46 inch box" in b2, etc.. A total of 1200 lines of
different items and descriptions. Then I have 4 quote sheets. Some rows for
each sheet use "VLOOKUP", so if the item number from the inventory sheet is
typed in appropriate col., description from inventory sheet is returned in
next col.. OK, next I have a sheet that is my picking ticket. On this sheet
I want a total by row of each item listed on the 4 quote sheets, so it shows
how many of the same item were listed on the 4 quote sheets. So if "80036"
is on quote sheet one with a value of 4, and on quote sheet two with a value
of 3, my picking tix would show that item number and description with a value
of 7 in the appropriate col.. Not all items will always be listed on quote
sheets. How do I set up the rows on picking tix to look for an item number
on the four quote sheets,and if not found, go to the next item number, once
an
item number is found, sum the total number listed for the found item number,
then on the next row, do the same thing without duplicating what has already
been found, until all 1200 items have been searched for? I think I am asking
too much..........TIA for any and all help.