# countifs help

#### Barto

##### Board Regular
Hi,

I'm trying to do a "countifs" formula that has to meet a few criteria and there is one criteria I'm not sure how to write (the other criteria are straight forward)

the criteria is if I find certain text in a cell but the text could be anywhere in the cell... at the start at the end in the middle..

For example if I'm looking for "VEA 01" how do I get it to count it if is appears somewhere in the cell

Example cell 1 = "VEA 05 VEA 03 VEA 01"
Example cell 2 = "VEA 01 VEA 06 VEA 04"
Example cell 3 = "VEA 05 VEA 01 VEA 04"
Example cell 4 = "VEA 05 VEA 01"
Example cell 5 = "VEA 01"

In all of the above examples VEA 01 has appeared somewhere in the cell so I want it counted..

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
maybe include this in your main formula...
ISNUMBER(search("*VEA 01*",A1,1)

Hi!

If you have the data in A1:A5, you can use this formula:

=COUNTIF(A1:A5,"*VEA 01*")

Blessings!

Try

Excel 2010
A
1VEA 05 VEA 03 VEA 01
2Bob
3VEA 05 VEA 03 VEA 03
4bob
5Bob
6VEA 05 VEA 03 VEA 06
7VEA 05 VEA 03 VEA 07
8Bob
9VEA 05 VEA 03 VEA 09
10VEA 05 VEA 03 VEA 10
11Bob
12Bob
13
146
Sheet2
Cell Formulas
RangeFormula
A14=COUNTIF(A1:A12,"*VEA 05*")

What if "VEA 05" was in a cell say g2? I tried =COUNTIF(A1:A12,"*"&g2&"*") but this only works if VEA 05 appears in the middle of the cell somewhere?

Try

Excel 2010
A
1VEA 05 VEA 03 VEA 01
2Bob
3VEA 05 VEA 03 VEA 03
4bob
5Bob
6VEA 05 VEA 03 VEA 06
7VEA 05 VEA 03 VEA 07
8Bob
9VEA 05 VEA 03 VEA 09
10VEA 05 VEA 03 VEA 10
11Bob
12Bob
13
146

</tbody>
Sheet2

Worksheet Formulas
CellFormula
A14=COUNTIF(A1:A12,"*VEA 05*")

</tbody>

<tbody>
</tbody>

Assume your data is in column A, and Column B is currently blank, then this VBA solution should work.

Code:
``````Option Explicit

Sub foo()
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
Dim i As Long
Dim tot As Long

For i = 1 To lr
If Range("A" & i) Like "*VEA 01*" Then
Range("B" & i) = 1
End If
Next i

tot = WorksheetFunction.Sum(Range("B1:B" & lr))
MsgBox ("Total VEA 01 =" & tot)

End Sub``````

To put more context be the VEA outcomes column shows how it populates each cell

and the first and second columns show the various outcomes and what the counts should equal
 Outcome Count should equal VEA CALL OUTCOMES VEA 01 - jump 2 VEA 01 - jump;VEA 04 -how to do itj;VEA 05 - over there;VEA 17 - check VEA 02 - due cause 4 VEA 04A - now;VEA 08 - entry level VEA 04 -how to do itj 1 VEA 02 - due cause VEA 04A - now 2 VEA 02 - due cause VEA 05 - over there;VEA 17 - check 1 VEA 29 - PV - Party Apps;VEA 33 - I/S & O/S General VEA 08 - entry level 1 VEA 02 - due cause;VEA 04A - now VEA 09 - Enrolment - Check 1 VEA 01 - jump;VEA 09 - Enrolment - Check VEA 17 - check 1 VEA 02 - due cause VEA 29 - PV - Party Apps 1 VEA 33 - I/S & O/S General 1

<colgroup><col><col><col></colgroup><tbody>
</tbody>

with corection
To put more context be the VEA outcomes column shows how it populates each cell

and the first and second columns show the various outcomes and what the counts should equal
 Outcome Count should equal VEA CALL OUTCOMES VEA 01 - jump 2 VEA 01 - jump;VEA 04 -how to do itj;VEA 05 - over there;VEA 17 - check VEA 02 - due cause 4 VEA 04A - now;VEA 08 - entry level VEA 04 -how to do itj 1 VEA 02 - due cause VEA 04A - now 2 VEA 02 - due cause VEA 05 - over there 1 VEA 29 - PV - Party Apps;VEA 33 - I/S & O/S General VEA 08 - entry level 1 VEA 02 - due cause;VEA 04A - now VEA 09 - Enrolment - Check 1 VEA 01 - jump;VEA 09 - Enrolment - Check VEA 17 - check 1 VEA 02 - due cause VEA 29 - PV - Party Apps 1 VEA 33 - I/S & O/S General 1

<TBODY>
</TBODY>

Try
Code:
``=COUNTIF(A1:A12,"*"&G2&"*")``

Try

Excel 2010
ABCDE
1OutcomeCount should equalVEA CALL OUTCOMES
2VEA 01 - jump2VEA 01 - jump;VEA 04 -how to do itj;VEA 05 - over there;VEA 17 - check
3VEA 02 - due cause4VEA 04A - now;VEA 08 - entry level
4VEA 04 -how to do itj1VEA 02 - due cause
5VEA 04A - now2VEA 02 - due cause
6VEA 05 - over there1VEA 29 - PV - Party Apps;VEA 33 - I/S & O/S General
7VEA 08 - entry level1VEA 02 - due cause;VEA 04A - now
8VEA 09 - Enrolment - Check1VEA 01 - jump;VEA 09 - Enrolment - Check
9VEA 17 - check1VEA 02 - due cause
10VEA 29 - PV - Party Apps1
11VEA 33 - I/S & O/S General1
Sheet3
Cell Formulas
RangeFormula
B2=COUNTIF(\$E\$2:\$E\$11,"*"&A2&"*")
B3=COUNTIF(\$E\$2:\$E\$11,"*"&A3&"*")
B4=COUNTIF(\$E\$2:\$E\$11,"*"&A4&"*")
B5=COUNTIF(\$E\$2:\$E\$11,"*"&A5&"*")
B6=COUNTIF(\$E\$2:\$E\$11,"*"&A6&"*")
B7=COUNTIF(\$E\$2:\$E\$11,"*"&A7&"*")
B8=COUNTIF(\$E\$2:\$E\$11,"*"&A8&"*")
B9=COUNTIF(\$E\$2:\$E\$11,"*"&A9&"*")
B10=COUNTIF(\$E\$2:\$E\$11,"*"&A10&"*")
B11=COUNTIF(\$E\$2:\$E\$11,"*"&A11&"*")

Replies
3
Views
114
Replies
5
Views
81
Replies
6
Views
197
Replies
5
Views
261
Replies
9
Views
309

1,203,082
Messages
6,053,419
Members
444,662
Latest member
AaronPMH

### 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.

### Which adblocker are you using?

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

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