# Combining CountIF and Vlookup funtion in IF Statement

1. ## Combining CountIF and Vlookup funtion in IF Statement

I need help with a problem I have please.

I have two separate sheets A and B.

I am trying to count the number of times a product appears in sheet B and the return this value in a cell in sheet A. sounds simple enough.

However a simple VLookup function is not working because the value that has to return to the cell in sheet A has to match 2 criteria along an X and Y axis.

I am trying to count the number of times the product appears in sheet B relating to a specific placement and bring the value back to Sheet A in the specific cell.

Here is an example of what I mean as the sheet i am working from has thousands of rows:

Sheet A

Product 2 Product 3 Product 4

Placement 1 value? value? value?

Placement 2 value? value? value?

Placement 3 value? value? value?

Sheet B

Placement 3 Product. 5

Placement 2 Product. 3

Placement 1 Product 3

Placement 2 Product. 3

Placement 3 Product 2

Placement 3 Product 2

Placement 1 Product 2

Placement 2 Product 4

Placement 3 Product 4

Any assistance would be very much appreciated.

Thanks,

I need help with a problem I have please.

I have two separate sheets A and B.

I am trying to count the number of times a product appears in sheet B and the return this value in a cell in sheet A. sounds simple enough.

However a simple VLookup function is not working
a simple VLookup is designed to fetch a cell adjacent to another cell the value of which is known, you do not appear to have this condition
because the value that has to return to the cell in sheet A has to match 2 criteria along an X and Y axis.

I am trying to count the number of times the product appears in sheet B relating to a specific placement
here's where the confusion gets better, are you trying to locate a cell based on a X and Y 'lookup' (simple enough) or, are you needing to count the number of occurances of an item based upon another criteria, asin how many widgets are in column C or how many widgets are along rows that have a specified value in A etc
and bring the value back to Sheet A in the specific cell.
If you are using a Formula the answer will appear in the cell in which the formula is placed.

Here is an example of what I mean as the sheet i am working from has thousands of rows:

Sheet A
Product 2 Product 3 Product 4
Placement 1 value? value? value?
Placement 2 value? value? value?
Placement 3 value? value? value?

Sheet B
Placement 3 Product. 5
Placement 2 Product. 3
Placement 1 Product 3
Placement 2 Product. 3
Placement 3 Product 2
Placement 3 Product 2
Placement 1 Product 2
Placement 2 Product 4
Placement 3 Product 4
Any assistance would be very much appreciated.
Thanks,

Notwithstanding that, assuming that SheetA headers are row1.

in B2 put

=SUMPRODUCT(--(SheetB!\$A\$1:\$A\$999=\$A2)*(--(SheetB!\$B\$1:\$B\$999=B\$1)))

and formula fill that two columns rightwards, then (with the three columns still selected) formula fill downwards for the number of placements.

hth
---

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1