Count and Lookup
 

I have two sheets. one contains a list of item IDs in the first column and
number of hits in the second column. The second sheet contains a list of item
IDs in the first column (the item ID may appear more then once within the
column) and the units shipped in the second column. The reason an ID may be
listed more then once is because each entry represents a different salkes
order where the ID appeared and the quantity shipped next to it represents
the quantity shipped on that particular order.

The problem I am having is figuring out a way to put a formula in the "hits"
column of the first sheet that would look up the ID from that same sheet and
count the total number of orders that ID shipped on from the second sheet.
All zeros should be omitted since they represent an order that did not ship.

Any suggestions?

Thanks.

Assuming that you want to total quantities shipped per ID...

=SUMIF(Sheet2!A:A,ID,Sheet2!B:B)

"Ginger" wrote in message
...
I have two sheets. one contains a list of item IDs in the first column and
number of hits in the second column. The second sheet contains a list of
item
IDs in the first column (the item ID may appear more then once within the
column) and the units shipped in the second column. The reason an ID may
be
listed more then once is because each entry represents a different salkes
order where the ID appeared and the quantity shipped next to it represents
the quantity shipped on that particular order.

The problem I am having is figuring out a way to put a formula in the
"hits"
column of the first sheet that would look up the ID from that same sheet
and
count the total number of orders that ID shipped on from the second sheet.
All zeros should be omitted since they represent an order that did not
ship.

Any suggestions?

Thanks.

Actually, I wanted to count the number of times an ID shipped...


"Aladin Akyurek" wrote:

Assuming that you want to total quantities shipped per ID...

=SUMIF(Sheet2!A:A,ID,Sheet2!B:B)

"Ginger" wrote in message
...
I have two sheets. one contains a list of item IDs in the first column and
number of hits in the second column. The second sheet contains a list of
item
IDs in the first column (the item ID may appear more then once within the
column) and the units shipped in the second column. The reason an ID may
be
listed more then once is because each entry represents a different salkes
order where the ID appeared and the quantity shipped next to it represents
the quantity shipped on that particular order.

The problem I am having is figuring out a way to put a formula in the
"hits"
column of the first sheet that would look up the ID from that same sheet
and
count the total number of orders that ID shipped on from the second sheet.
All zeros should be omitted since they represent an order that did not
ship.

Any suggestions?

Thanks.

"Ginger" wrote in message
...
Actually, I wanted to count the number of times an ID shipped...


=COUNTIF(Sheet2!A:A,ID)

might suffice.

With ID's in A and Shipped Quantities in B, try:

=SUMPRODUCT((Sheet2!A2:A100=ID)*(Sheet2!B2:B1000) )

Tim C

"Ginger" wrote in message
...
Actually, I wanted to count the number of times an ID shipped...


"Aladin Akyurek" wrote:

Assuming that you want to total quantities shipped per ID...

=SUMIF(Sheet2!A:A,ID,Sheet2!B:B)

"Ginger" wrote in message
...
I have two sheets. one contains a list of item IDs in the first column
and
number of hits in the second column. The second sheet contains a list
of
item
IDs in the first column (the item ID may appear more then once within
the
column) and the units shipped in the second column. The reason an ID
may
be
listed more then once is because each entry represents a different
salkes
order where the ID appeared and the quantity shipped next to it
represents
the quantity shipped on that particular order.

The problem I am having is figuring out a way to put a formula in the
"hits"
column of the first sheet that would look up the ID from that same
sheet
and
count the total number of orders that ID shipped on from the second
sheet.
All zeros should be omitted since they represent an order that did not
ship.

Any suggestions?

Thanks.

Neither of these solutions seem to be working. Perhaps a sample will help.

Sheet 1:
ID Hits
111
112
113

Sheet 2:

ID Shipped
111 20
111 10
111 0
111 7
112 2
112 0
113 6
113 5
113 8
113 34

So the result I'm hoping to get in Sheet 1 are as follows:

Sheet 1:
ID Hits
111 3
112 1
113 4

Basically I'm counting the number of times each item shipped excluding the
zeros since they represent a time when the item was quoted to a customer but
not shipped to them.

"Tim C" wrote:

With ID's in A and Shipped Quantities in B, try:

=SUMPRODUCT((Sheet2!A2:A100=ID)*(Sheet2!B2:B1000) )

Tim C

"Ginger" wrote in message
...
Actually, I wanted to count the number of times an ID shipped...


"Aladin Akyurek" wrote:

Assuming that you want to total quantities shipped per ID...

=SUMIF(Sheet2!A:A,ID,Sheet2!B:B)

"Ginger" wrote in message
...
I have two sheets. one contains a list of item IDs in the first column
and
number of hits in the second column. The second sheet contains a list
of
item
IDs in the first column (the item ID may appear more then once within
the
column) and the units shipped in the second column. The reason an ID
may
be
listed more then once is because each entry represents a different
salkes
order where the ID appeared and the quantity shipped next to it
represents
the quantity shipped on that particular order.

The problem I am having is figuring out a way to put a formula in the
"hits"
column of the first sheet that would look up the ID from that same
sheet
and
count the total number of orders that ID shipped on from the second
sheet.
All zeros should be omitted since they represent an order that did not
ship.

Any suggestions?

Thanks.

In looking at your example, there's nothing wrong with Aladin's suggestion!

I'm sure he assumed you would replace "ID" with the proper cell location
(address).

=COUNTIF(Sheet2!A:A,A2)
Entered in B2 of Sheet1

Drag down to copy as needed.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Ginger" wrote in message
...
Neither of these solutions seem to be working. Perhaps a sample will help.

Sheet 1:
ID Hits
111
112
113

Sheet 2:

ID Shipped
111 20
111 10
111 0
111 7
112 2
112 0
113 6
113 5
113 8
113 34

So the result I'm hoping to get in Sheet 1 are as follows:

Sheet 1:
ID Hits
111 3
112 1
113 4

Basically I'm counting the number of times each item shipped excluding the
zeros since they represent a time when the item was quoted to a customer but
not shipped to them.

"Tim C" wrote:

With ID's in A and Shipped Quantities in B, try:

=SUMPRODUCT((Sheet2!A2:A100=ID)*(Sheet2!B2:B1000) )

Tim C

"Ginger" wrote in message
...
Actually, I wanted to count the number of times an ID shipped...


"Aladin Akyurek" wrote:

Assuming that you want to total quantities shipped per ID...

=SUMIF(Sheet2!A:A,ID,Sheet2!B:B)

"Ginger" wrote in message
...
I have two sheets. one contains a list of item IDs in the first column
and
number of hits in the second column. The second sheet contains a list
of
item
IDs in the first column (the item ID may appear more then once within
the
column) and the units shipped in the second column. The reason an ID
may
be
listed more then once is because each entry represents a different
salkes
order where the ID appeared and the quantity shipped next to it
represents
the quantity shipped on that particular order.

The problem I am having is figuring out a way to put a formula in the
"hits"
column of the first sheet that would look up the ID from that same
sheet
and
count the total number of orders that ID shipped on from the second
sheet.
All zeros should be omitted since they represent an order that did
not
ship.

Any suggestions?

Thanks.

This formula does work, however it does not omit from the count the IDs that
have a zero in the corresponding column B cell.



"RagDyeR" wrote:

In looking at your example, there's nothing wrong with Aladin's suggestion!

I'm sure he assumed you would replace "ID" with the proper cell location
(address).

=COUNTIF(Sheet2!A:A,A2)
Entered in B2 of Sheet1

Drag down to copy as needed.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Ginger" wrote in message
...
Neither of these solutions seem to be working. Perhaps a sample will help.

Sheet 1:
ID Hits
111
112
113

Sheet 2:

ID Shipped
111 20
111 10
111 0
111 7
112 2
112 0
113 6
113 5
113 8
113 34

So the result I'm hoping to get in Sheet 1 are as follows:

Sheet 1:
ID Hits
111 3
112 1
113 4

Basically I'm counting the number of times each item shipped excluding the
zeros since they represent a time when the item was quoted to a customer but
not shipped to them.

"Tim C" wrote:

With ID's in A and Shipped Quantities in B, try:

=SUMPRODUCT((Sheet2!A2:A100=ID)*(Sheet2!B2:B1000) )

Tim C

"Ginger" wrote in message
...
Actually, I wanted to count the number of times an ID shipped...


"Aladin Akyurek" wrote:

Assuming that you want to total quantities shipped per ID...

=SUMIF(Sheet2!A:A,ID,Sheet2!B:B)

"Ginger" wrote in message
...
I have two sheets. one contains a list of item IDs in the first column
and
number of hits in the second column. The second sheet contains a list
of
item
IDs in the first column (the item ID may appear more then once within
the
column) and the units shipped in the second column. The reason an ID
may
be
listed more then once is because each entry represents a different
salkes
order where the ID appeared and the quantity shipped next to it
represents
the quantity shipped on that particular order.

The problem I am having is figuring out a way to put a formula in the
"hits"
column of the first sheet that would look up the ID from that same
sheet
and
count the total number of orders that ID shipped on from the second
sheet.
All zeros should be omitted since they represent an order that did
not
ship.

Any suggestions?

Thanks.

Sorry !!!
I missed that.

So, it's the same story with Tim C's suggestion.

His assumption that you would replace the "ID" with
A2

=SUMPRODUCT((Sheet2!A2:A100=A2)*(Sheet2!B2:B1000) )


HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Ginger" wrote in message
...
This formula does work, however it does not omit from the count the IDs that
have a zero in the corresponding column B cell.



"RagDyeR" wrote:

In looking at your example, there's nothing wrong with Aladin's
suggestion!

I'm sure he assumed you would replace "ID" with the proper cell location
(address).

=COUNTIF(Sheet2!A:A,A2)
Entered in B2 of Sheet1

Drag down to copy as needed.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Ginger" wrote in message
...
Neither of these solutions seem to be working. Perhaps a sample will help.

Sheet 1:
ID Hits
111
112
113

Sheet 2:

ID Shipped
111 20
111 10
111 0
111 7
112 2
112 0
113 6
113 5
113 8
113 34

So the result I'm hoping to get in Sheet 1 are as follows:

Sheet 1:
ID Hits
111 3
112 1
113 4

Basically I'm counting the number of times each item shipped excluding the
zeros since they represent a time when the item was quoted to a customer
but
not shipped to them.

"Tim C" wrote:

With ID's in A and Shipped Quantities in B, try:

=SUMPRODUCT((Sheet2!A2:A100=ID)*(Sheet2!B2:B1000) )

Tim C

"Ginger" wrote in message
...
Actually, I wanted to count the number of times an ID shipped...


"Aladin Akyurek" wrote:

Assuming that you want to total quantities shipped per ID...

=SUMIF(Sheet2!A:A,ID,Sheet2!B:B)

"Ginger" wrote in message
...
I have two sheets. one contains a list of item IDs in the first
column
and
number of hits in the second column. The second sheet contains a
list
of
item
IDs in the first column (the item ID may appear more then once
within
the
column) and the units shipped in the second column. The reason an
ID
may
be
listed more then once is because each entry represents a different
salkes
order where the ID appeared and the quantity shipped next to it
represents
the quantity shipped on that particular order.

The problem I am having is figuring out a way to put a formula in
the
"hits"
column of the first sheet that would look up the ID from that same
sheet
and
count the total number of orders that ID shipped on from the second
sheet.
All zeros should be omitted since they represent an order that did
not
ship.

Any suggestions?

Thanks.