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:

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant


If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If


aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

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.

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.