COUNTIF with multiple criteria

[sakis_s]

Hi! I'm trying to create a folrmula to search in different columns and if all cretiria match, then it will return the result.
For example:

A	B	C	D	E	F	G
1	kids cushioned sportive socks 2p		kids	socks	2p	2 pack socks for kids

If B1 contains all word cretiria from columns D,E,F should return G result.

I tried something like this:

=LOOKUP(PI();1/COUNTIF("*";B1;"*"(D:D&"*";E:E&"*";F:F&"*");G:G)

and many other combinations of this formula but it doesn't work.

Any help would much appreciated!

 

[Anthony47]

Maybe:

=IF(ISERROR(PRODUCT(FIND(D1:F2,B1))),"",G1)

Formula to be confirmed using the keys Contr-Shift-Enter, nor Enter alone

 

[sakis_s]

Maybe:

=IF(ISERROR(PRODUCT(FIND(D1:F2,B1))),"",G1)

Formula to be confirmed using the keys Contr-Shift-Enter, nor Enter alone

Thanks for your response!

I got syntax error when changing it to my needs. The reason i tried the formula mentioned above, is because it looks in different creteria in columns D,E,F and G.
So the range to look for matching creteria has to be the whole D:D & E:E & F:F and when it finds all three matches in same row for example in row 13 (D13+E13+F13) then it should return the G13 cell.
Also the creteria in the cell that it looks (B1) could be in any position of the sentence together with other words.

 

[Anthony47]

Are your needs different than what posted in message #1? How did you modify the formula?
If you have many rows of data, you will set that formula in the first row and then copy it down

 

[sakis_s]

Are your needs different than what posted in message #1? How did you modify the formula?
If you have many rows of data, you will set that formula in the first row and then copy it down

I'm sorry it's confusing. I'll try to explain it better so here it is:

	A	B	C	D	E	F
1	kids cushioned sportive socks 2p	Formula goes here	kids	socks	4p	4 pack socks for kids
2			men	socks	3p	3 pack socks for men
3			kids	tights	2p	2 pack tights for kids
4			kids	socks	2p	2 pack socks for kids

So now formula is in B1 Cell. My goal is to search text in A1 for all 3 matching creteria in columns C, D & E and then return the F result when all 3 creteria exist.
In my example formula in B1 cell should return "2 pack socks for kids" because all creteria in C4+D4+E4 exist in text inside A1 cell.

 

[sakis_s]

So anyone knows the answer?

 

[Anthony47]

You love hiding the structure of your data...
Rather then going back and forth with Q & A let me go with a customized function, that corresponds to the following code:

Function GimmeLuck(ByRef Blk1 As Range, ByRef Blk2 As Range) As Variant
Dim oArr() As String, K As Long, B2CC As Long
Dim I As Long, AllIn As Long
'
ReDim oArr(1 To Blk1.Rows.Count, 1 To 1)
B2CC = Blk2.Columns.Count
For I = 1 To Blk1.Rows.Count
    If Blk1.Cells(I, 1) <> "" Then
        For j = 0 To 20
            If (j > 0 And Blk1.Cells(I + j, 1) <> "") Or Blk2.Cells(I + j, 1) = "" Then Exit For
            AllIn = 1
            For K = 1 To B2CC - 1
                AllIn = InStr(1, Blk1.Cells(I, 1), Blk2.Cells(I + j, K), vbTextCompare) * AllIn
                If AllIn = 0 Then Exit For
            Next K
            If AllIn <> 0 Then
                oArr(I, 1) = Blk2.Cells(I + j, B2CC)
            End If
            If oArr(I, 1) <> "" Then Exit For
        Next j
    End If
Next I
GimmeLuck = oArr
End Function

Copy the code into a standard module of your vba project.
Then return to Excel and you shall see that the function GimmeLuck is now available.
Its syntax:
GimmeLuck(FirstColumnRange, CriteriaAndResultRange)

For example:
=GimmeLuck(A1:A20, C1:C25)

The two ranges must start from the same row but can have different height because there are several rows of criterias after the last value in column A

The formula must be written as an array formula:
-select in column B from the first row of data to the last row used as FirstColumnRange; so B1:B20 with the given example
-insert the formula in the formula bar
-confirm the formula using the keys Contr-Shift-Enter, not Enter alone

If this doesn’t fit your need then you have to explain wich is the layout of your data and which results you need and where

 

[sakis_s]

You love hiding the structure of your data...
Rather then going back and forth with Q & A let me go with a customized function, that corresponds to the following code:

Function GimmeLuck(ByRef Blk1 As Range, ByRef Blk2 As Range) As Variant
Dim oArr() As String, K As Long, B2CC As Long
Dim I As Long, AllIn As Long
'
ReDim oArr(1 To Blk1.Rows.Count, 1 To 1)
B2CC = Blk2.Columns.Count
For I = 1 To Blk1.Rows.Count
    If Blk1.Cells(I, 1) <> "" Then
        For j = 0 To 20
            If (j > 0 And Blk1.Cells(I + j, 1) <> "") Or Blk2.Cells(I + j, 1) = "" Then Exit For
            AllIn = 1
            For K = 1 To B2CC - 1
                AllIn = InStr(1, Blk1.Cells(I, 1), Blk2.Cells(I + j, K), vbTextCompare) * AllIn
                If AllIn = 0 Then Exit For
            Next K
            If AllIn <> 0 Then
                oArr(I, 1) = Blk2.Cells(I + j, B2CC)
            End If
            If oArr(I, 1) <> "" Then Exit For
        Next j
    End If
Next I
GimmeLuck = oArr
End Function

Copy the code into a standard module of your vba project.
Then return to Excel and you shall see that the function GimmeLuck is now available.
Its syntax:
GimmeLuck(FirstColumnRange, CriteriaAndResultRange)

For example:
=GimmeLuck(A1:A20, C1:C25)

The two ranges must start from the same row but can have different height because there are several rows of criterias after the last value in column A

The formula must be written as an array formula:
-select in column B from the first row of data to the last row used as FirstColumnRange; so B1:B20 with the given example
-insert the formula in the formula bar
-confirm the formula using the keys Contr-Shift-Enter, not Enter alone

If this doesn’t fit your need then you have to explain wich is the layout of your data and which results you need and where

Really thank you for trying to help.
I get "Ambiguous name detected: GimmeLuck" when hitting ctrl+shift+enter.
Do you know why this happens?

 

[Anthony47]

"Ambiguous name detected:GimmeLuck"....
No idea...
Did you insert the code into your vba project?
Did you set the formula following the instructions I gave:

-select in column B from the first row of data to the last row used as FirstColumnRange; so B1:B20 with the given example
-insert the formula in the formula bar
-confirm the formula using the keys Contr-Shift-Enter, not Enter alone

Which formula you tried?