+ Reply to Thread
Results 1 to 2 of 2

Combining CountIF and Vlookup funtion in IF Statement

  1. #1
    Registered User
    Join Date
    11-29-2006
    Posts
    5

    Exclamation 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,

    Adrian
    Last edited by Adrian2006g; 11-29-2006 at 01:03 PM.

  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote Originally Posted by Adrian2006g
    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,

    Adrian
    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
    ---
    Si fractum non sit, noli id reficere.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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