vlookup to count based on criteria?

  • Re: vlookup to count based on criteria?

    Try these:

    in D3:

    [COLOR="#0000FF"]=IF(A3=A2,"",COUNTIF('Linked Products'!$A$2:$A$11,$B3))[/COLOR]

    copied down

    in E3:

    [COLOR="#0000FF"]=IF(A3=A2,"",SUMPRODUCT(--(ISNUMBER(MATCH('Linked Products'!$B$2:$B$11,$B$3:$B$11,0))),--('Linked Products'!$A$2:$A$11=B3)))[/COLOR]

    for column F, we need a user defined function to be able to concatenate... .so you will need to add a macro. Hit Alt+F11, then go to Insert> Module then paste this in the editor:

    Then back in F3, enter Array Formula*:

    [COLOR="#0000FF"]=SUBSTITUTE(TRIM(aconcat(IF(ISNUMBER(MATCH('Linked Products'!$B$2:$B$11,$B$3:$B$11,0))*('Linked Products'!$A$2:$A$11=B3),'Linked Products'!$B$2:$B$11,"")," "))," ",",")[/COLOR]

    Copied down

    Note also: You will need to save the workbook as a .xlsm workbook.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: vlookup to count based on criteria?


    You were referencing columns A and B of the Related sheet in your formulas when you should have been referencing columns B and C.

    Also, your ranges are quite large and will contribute to slow processing of your formulas...

    I applied a dynamic named range called "Related" to your related sheet table. This will allow the table to grow or shrink and still be referenced in your formulas.

    I also shrunk the references in your main sheet to 6500 rows.... you should keep this to minimum needed...

    Please see attached. Warning... it will take a while to recalculate.

    If you need it to process faster, then you'll need someone to provide you a vba solution or rethink the process.

  • Re: vlookup to count based on criteria?

    It doesn't calculate correctly.

    For order [TABLE="width: 87"]


    [TD="class: xl63, width: 87"]O0674578[/TD]


    column P should be 0 and Q should be blank because no linked skus were purchased.

    Also there are no calculations in column O

    Also for sku 31193, cell N4 should be 0.

    For order O0665619 P5 should be 1 and Q5 should be 10027


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!