Can a function be written in Excel to count all of the cells in a range that contains a certain set of letters?

=COUNTIF(A:A,"*"&"ABC"&"*")

this will count everything that has "ABC" in column A

If you have the "ABC" in cell B1, use this

=COUNTIF(A:A,"*"&B1&"*")

Here is another approach. The following macro will display a message box with the number of cells containing the text string entered by the user, in the selected range.

Copy the following macro to the clipboard:

Sub Spl_Countif()

Dim ctr

fString = UCase(InputBox("Enter string to evaluate.", "Search For"))

If fString = "" Then

Exit Sub

End If

For Each cell In Selection

If InStr("," & fString & ",", cell) And cell.Value <> "" Then

ctr = ctr + 1

End If

Next

If ctr > 0 Then

MsgBox "There are " & ctr & " instance(s) of " & _

UCase(fString), vbOKOnly, "Result"

ElseIf ctr = 0 Then

MsgBox UCase(fString) & " does not appear in the selected range.", _

vbOKOnly, "Not Found"

End If

End Sub

Press ALT + F11

In the menus at the top of the VBE, select INSERT >MODULE

Paste the macro into the editing area to the right.

Close the VBE and return to the worksheet.

Press ALT + F8

When the Macros window opens, highlight the macro and click 'Options..'

Enter a letter to be used as a keyboard shortcut and click 'OK'.

Close the Macros window.

Highlight a range of cells to evaluate. It can be an entire row or column (by clicking the row number or column letter). It can be a range of cells of any dimension, such as F5:M224. You can select multiple rows by pressing Ctrl and the row numbers you wish to include. Ditto column letters.

Once the range is selected, press CTRL + your shortcut letter and the macro will query for the search criteria. Enter the text string, such as "ABC" (no quotes) and click 'OK'. The result will be displayed. The macro is not case sensitive. It will count ABC, Abc, abc, aBc, and abC as meeting the criteria to be included in the count.

Note: You state that the ABC is separated by commas and would thus appear as ',ABC,'. If this is not truly accurate and the ABC appears randomly, modify the macro before copying:

Change:

If InStr("," & fString & ",", cell) And cell.Value <> "" Then

To:

If InStr(fString, cell) And cell.Value <> "" Then

=COUNTIF(loc1:loc2"*"&"ABC"&"*")

Example: =COUNTIF(A1:F8"*"&"ABC"&"*") would count the number of times ABC exists in the cell range A1 to F8. You can add more areas if necessary with a comma separated list, as in =COUNTIF(A1:F8,G2:G4"*"&"ABC"&"*)

If this doesn't work on your spreadsheet, consider the following solutions.

1. Using Find

If the search string is ABC, simply search the workbook or page for ABC. You can find this feature in the edit menu, or just type Ctrl-F. This will bring up the number of cells that ABC is found in. You'll have to manually count the cells this way.

2. Using a Macro

The other way is to create a macro. However, I do not have sufficient knowledge to cover that task. If you don't want to manually count the cells, you'll have to resort to this method.

Hope this helps,

TeamVista

for example the answer, enable's assume you have 20 documents that must be examined. you're able to desire to create a clean column. For this occasion we could use Column L. you want a formula to be certain if Bn="Apr" and Kn="particular" the place n is the row form of each and every documents. you will get a consequence of the two a real or fake. See occasion below: COLUMN L =AND(B1="Apr",K1="particular") Then drag the series to amplify formula i.e. =AND(B2="Apr",K2="particular") ... =AND(B20="Apr",K20="particular") Afterwards use =COUNTIF(L1:L20,"real") formula and you have the potential to count variety what number real solutions you get. this could sparkling up your issue. :)