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

trikiash

New Member
Joined
Feb 22, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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!!
 

Attachments

  • excel question.PNG
    excel question.PNG
    16.9 KB · Views: 12

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,013
Office Version
  1. 2016
Platform
  1. Windows
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
ABC
1apple orange pineapple apple
2pear apple banana
3apple orange pineapple
4banana
5pear apple banana apple
6pineapple
7apple orange pineapple
8orange banana banana
9pineapple orange orange
10apple orange pineapple
11banana
12
13
14
15appleorange8
16pineapple6
17banana5
18orange6
Sheet1
Cell Formulas
RangeFormula
C15C15=COUNTIF($A$1:$A$11,"*"&A15&"*")+IF(B15<>"",COUNTIF($A$1:$A$11,"*"&B15&"*"),0)-$C$16
C16:C18C16=COUNTIF($A$1:$A$11,"*"&A16&"*")+IF(B16<>"",COUNTIF($A$1:$A$11,"*"&B16&"*"),0)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,989
Office Version
  1. 365
Platform
  1. Windows
Another option
+Fluff New.xlsm
ABC
1apple orange pineapple apple
2pear apple banana
3apple orange pineapple
4banana
5pear apple banana apple
6pineapple
7apple orange
8orange banana banana
9pineapple orange orange
10apple orange pineapple
11banana
12
13
14
15appleorange8
16pineapple5
17banana5
18orange6
19apple6
20rang0
Main
Cell Formulas
RangeFormula
C15:C20C15=SUMPRODUCT(--((ISNUMBER(SEARCH(" "&A15&" "," "&$A$1:$A$11&" ")))+(ISNUMBER(SEARCH(" "&IF(B15="","XXXX",B15)&" "," "&$A$1:$A$11&" ")))>0))
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,585
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
AB
1apple orange pineapple apple
2pear apple banana
3apple orange pineapple
4banana
5pear apple banana apple
6pineapple
7apple orange pineapple
8orange banana banana
9pineapple orange orange
10apple orange pineapple
11banana
12
13
14
15apple or orange8
16pineapple6
17banana5
18orange6
Sheet1
Cell Formulas
RangeFormula
B15:B18B15=trikiash($A$1:$A$11,A15)

VBA Code:
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,545

ADVERTISEMENT

Another formula option:

Book1
ABC
1apple orange pineapple apple
2pear apple banana
3apple orange pineapple
4banana
5pear apple banana apple
6pineapple
7apple orange pineapple
8orange banana banana
9pineapple orange orange
10apple orange pineapple
11banana
12
13
14
15appleorange8
16pineapple6
17banana5
18orange6
Sheet6
Cell Formulas
RangeFormula
C15:C18C15=SUMPRODUCT(--(MMULT(--ISNUMBER(SEARCH(" "&A15:B15&" "," "&$A$1:$A$11&" ")),{1;1})>0))
 

trikiash

New Member
Joined
Feb 22, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,989
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Forum statistics

Threads
1,144,370
Messages
5,723,966
Members
422,529
Latest member
mbilal429

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top