Need help with a formula...I think using countif example posted

[trikiash]

Hi all, hoping someone can help me using the attached example. I need a formula that will count how many times "apple" or "orange" appears....but I do not want it to count more than once in the same cell. So for this example, in A1 "apple" appears twice and "orange" appears once - I only want the formula to count it as ONE.
I thought I could use countif......

Thank you for any help!!

 

[Toadstool]

Hi Trikiash,

Using "apple or orange" as search criteria would add complexity so I've split into 2 columns.

You can do this with a wildcard search but as "apple" is contained in "pineapple" then it will also count, so for cell C15 I've subtracted the count for "pineapple". If that type of challenge may also happen in your real data then again it would add complexity to solve.

Book1
	A	B	C
1	apple orange pineapple apple
2	pear apple banana
3	apple orange pineapple
4	banana
5	pear apple banana apple
6	pineapple
7	apple orange pineapple
8	orange banana banana
9	pineapple orange orange
10	apple orange pineapple
11	banana
12
13
14
15	apple	orange	8
16	pineapple		6
17	banana		5
18	orange		6
Sheet1

Cell Formulas
Range	Formula
C15	C15	=COUNTIF($A$1:$A$11,"*"&A15&"*")+IF(B15<>"",COUNTIF($A$1:$A$11,"*"&B15&"*"),0)-$C$16
C16:C18	C16	=COUNTIF($A$1:$A$11,"*"&A16&"*")+IF(B16<>"",COUNTIF($A$1:$A$11,"*"&B16&"*"),0)

 

[Fluff]

Another option

+Fluff New.xlsm
	A	B	C
1	apple orange pineapple apple
2	pear apple banana
3	apple orange pineapple
4	banana
5	pear apple banana apple
6	pineapple
7	apple orange
8	orange banana banana
9	pineapple orange orange
10	apple orange pineapple
11	banana
12
13
14
15	apple	orange	8
16	pineapple		5
17	banana		5
18	orange		6
19	apple		6
20	rang		0
Main

Cell Formulas
Range	Formula
C15:C20	C15	=SUMPRODUCT(--((ISNUMBER(SEARCH(" "&A15&" "," "&$A$1:$A$11&" ")))+(ISNUMBER(SEARCH(" "&IF(B15="","XXXX",B15)&" "," "&$A$1:$A$11&" ")))>0))

 

[JoeMo]

If you don't want to split the apple or orange into 2 columns, here's a UDF you can try. Use it like a worksheet function after you install it (see the example of its use below).
To install the UDF:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and click on it.
3. On the VBE menu: Insert>Module
4. Copy the UDF from your browser window and paste it into the white space in the VBE window.
5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
6. Use the UDF as you would any native Excel function.
7. Make sure you have enabled macros whenever you open the file or the code will not run.

Book1.xlsm
	A	B
1	apple orange pineapple apple
2	pear apple banana
3	apple orange pineapple
4	banana
5	pear apple banana apple
6	pineapple
7	apple orange pineapple
8	orange banana banana
9	pineapple orange orange
10	apple orange pineapple
11	banana
12
13
14
15	apple or orange	8
16	pineapple	6
17	banana	5
18	orange	6
Sheet1

Cell Formulas
Range	Formula
B15:B18	B15	=trikiash($A$1:$A$11,A15)

Function trikiash(Rdata As Range, LookFor As String) As Long
'assumes strings in Rdata of more than one word are space delimited
'assumes LookFor is either a single word or if multiple words they are separated by " or "
Dim Cdata As Range, Vdata As Variant, i As Long, VLookFor As Variant
If InStr(1, LookFor, "or", vbTextCompare) = 0 Then
    For Each Cdata In Rdata
        Vdata = Split(Cdata.Value, " ")
        For i = LBound(Vdata) To UBound(Vdata)
            If Vdata(i) = LookFor Then
                trikiash = trikiash + 1
                Exit For
            End If
        Next i
    Next Cdata
Else
    VLookFor = Split(LookFor, " or ")
    For Each Cdata In Rdata
        Vdata = Split(Cdata.Value, " ")
        For i = LBound(Vdata) To UBound(Vdata)
            For j = LBound(VLookFor) To UBound(VLookFor)
                If Vdata(i) = VLookFor(j) Then
                    trikiash = trikiash + 1
                    GoTo Nx
                End If
            Next j
        Next i
Nx: Next Cdata
End If
End Function

 

[Eric W]

Another formula option:

Book1
	A	B	C
1	apple orange pineapple apple
2	pear apple banana
3	apple orange pineapple
4	banana
5	pear apple banana apple
6	pineapple
7	apple orange pineapple
8	orange banana banana
9	pineapple orange orange
10	apple orange pineapple
11	banana
12
13
14
15	apple	orange	8
16	pineapple		6
17	banana		5
18	orange		6
Sheet6

Cell Formulas
Range	Formula
C15:C18	C15	=SUMPRODUCT(--(MMULT(--ISNUMBER(SEARCH(" "&A15:B15&" "," "&$A$1:$A$11&" ")),{1;1})>0))

 

[trikiash]

Thank you all very much for the wonderful help! I will apply the various methods to my real data, and see which will be best. Again, THANK YOU!!!

 

[Fluff]

Glad we could help & thanks for the feedback.