Conditional counting of a string in 2 columns

Hi!

I've searched the internet to answer my question but unfortunately had no luck. Maybe you can help me find a solution to my task.

I know how to count how many times a string appears in a column:
=(SUM(LEN(A1:A40))-SUM(LEN(SUBSTITUTE(A1:A40,"search string 1",))))/LEN("search string 1")

But I need to count only if other column (eg. B) contains "search string 2". For example I want to know how many (green, red, golden) "APPLES" were sold to (Miss, missis, mister) "BROWN".

I had a vague idea about using an asterisk for joining two "SUM" formulae. But I couldn't do it without getting an error.

Is it possible? I'm sure there is some way.

Can you please give me a hint how to do it?

Thanks in advance.

would something like this work, given that you have xl2007:
=COUNTIFS(A:A,"*apple*",B:B,"*brown*")

Thanks, but countif counts only cells that 'contain' a string, I have many "apples" and "browns" in a single cell, I need a string search.

Give us a dummy workbook with possible cases on a range of cells and then show us what results you want.

Try this array function

=SUM(IF(ISNUMBER(SEARCH(C2,B1:B40)),(LEN(A1:A40)-LEN(SUBSTITUTE(A1:A40,C1,""))),0))/LEN(C1)
where C1 contains apple and C2 contains Brown
Does that work for you? I get 4.

Try this, given you are using 2007

=SUMPRODUCT((LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,"Apple","")))/LEN("Apple"),(LEN(B1:B10)-LEN(SUBSTITUTE(B1:B10,"Brown","")))/LEN("Brown"))

Or, a more generic version using drop-downs

=IFERROR(SUMPRODUCT((LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,G4,"")))/LEN(G4),(LEN(B1:B10)-LEN(SUBSTITUTE(B1:B10,H4,"")))/LEN(H4)),"")

A dummy workbook is just a sample of your workbook, reduced in size to show typical details, and with any senstitve data disguised. Usually a before and after scenario is useful

A picture rarely gets an enthusiastic response.

See the Attached

I can't see how to do what you are asking for without using VBa

Try this UDF

Option Explicit

Function SumProduceCustomer(rng As Range, strFruit As String, strCustomer As String)
    Dim n As Long, ctr As Long
    Dim Cell As Range
    Dim arrFruit As Variant, arrCustomer As Variant
    
    Application.Volatile
    
    For Each Cell In rng
        arrFruit = Split(Cell, Chr(10))
        arrCustomer = Split(Cell.Offset(0, 1), Chr(10))
        For n = 0 To UBound(arrFruit)
            If InStr(1, arrFruit(n), strFruit) > 0 And InStr(1, arrCustomer(n), strCustomer) > 0 Then ctr = ctr + 1
        Next
    Next
    SumProduceCustomer = ctr
End Function

When using this function specify the range by the first column only
e.g.

=SumProduceCustomer(A1:A3,"Apple","Brown")

or

=SumProduceCustomer(A1:A3,G4,H4)

This is entirely dependent on the adjacent cells in the list having the same number of lines (Alt+Enter) i.e. Chr(10) is used to split the data.

Hope this helps.