Count and Lookup

[Guest]

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.

 

[Aladin Akyurek]

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

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

 

[Guest]

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

 

[Aladin Akyurek]

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

=COUNTIF(Sheet2!A:A,ID)

might suffice.

 

[Tim C]

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

=SUMPRODUCT((Sheet2!A2:A100=ID)*(Sheet2!B2:B100>0))

Tim C

 

[Guest]

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.

 

[RagDyeR]

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!
==============================================

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.

 

[Guest]

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]

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:B100>0))


HTH,

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


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