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
    [ARF]x[/ARF]


    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?


    Hi,


    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"]

    [tr]


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

    [/tr]


    [/TABLE]
    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


    Thanks

Participate now!

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