# Question on Countif

#### faram

##### New Member
Hi,

I've been wondering how to use countif with numerical value.
I want to search in a Column the number of '1' so I'm doing Countif(Tableau[1].plage,1) which is working but the tricky thing is that sometimes there is 2 or 3 values in a cell (like '1, 2' or '2, 3' ...) so when I do the formula I get only the cells with just '1' not the ones with multiple values including 1.

So I tried doing multiples things :
Countif(Tableau[1].plage,"*"&1&"*")
Countif(Tableau[1].plage,"*1*")
But these formulas only count the cells with ONLY multiples values.

I can reach the result I want by doing Countif(Tableau[1].plage,1)+Countif(Tableau[1].plage,"*"&1&"*")
but I'm wondering if it's possible without 2 formulas.

Thanks for your help !

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### Tetra201

##### MrExcel MVP
Maybe
=SUM(Countif(Tableau[1].plage,{1,"*1*"}))

#### jasonb75

##### Well-known Member
The problem that you face here is that you have a mixture of numbers and text (cells with multiple numbers comma separated are text). Something that you might find with using "*"&1&"*" is that it will also count 10,11,12....21,31, etc which may not be desirable.

I think that this should allow for all possibilities, but it depends on the consistency of the entries in the table.
Excel Formula:
``=SUM(COUNTIF(Tableau[1].plage,{1,"1,*","*, 1","*, 1,*"}))``

#### alz

##### Board Regular
{=COUNT(SEARCH(1,Tableau[1].plage))}

#### faram

##### New Member

Thanks a lot for your time and help !
So I tried =Countif(Tableau[1].plage,{1,"*1*"}) but I got an error for the criteria part with {1,"*1*"} it is not accepted :'(

To give you visibility,

I have waves and zones and I would like in the future count the number in wave 1 and zone 2 for example
I want to achieve countifs(Tableau[1].wave, ???,Tableau[1].zone, scope) (it's in vba so scope is a variable with the zone),
The second part with the zone is working but I'm lacking this wave selection.

Thanks again !

#### jasonb75

##### Well-known Member
All of the suggestions should work fine. If the formula is not being accepted then it would mean either that there is an error in the table name, or that the regional format of the formula is incorrect.

All details that we took from the example formula that you provided in post 1.

#### faram

##### New Member

Thanks, yes it was a regional format difference.
Lastly do you know how to write it in VBA ?

I tried WorksheetFunction.Sum(WorksheetFunction.CountIf(Feuil5.Columns(6), {1,"*" & 1 & "*" }))
WorksheetFunction.Sum(WorksheetFunction.CountIf(Feuil5.Columns(6), "{"&1,"*"& 1 &"*" & "}"))
But all led to errors

I'm probably missing a syntax there ...

Thanks again for your help !

#### jasonb75

##### Well-known Member
I find that 'Evaluate' is easier to write as a formula in vba if you need to, although there are still going to be some differences.

Noting the comments that I made in post 3, which version of the formula will work best for your requirements?

#### jasonb75

##### Well-known Member
I noticed that you you flagged my first reply as the solution after I asked which version of the formula would be best for what you need, not sure if you still need a vba version of it but here it is just in case.
VBA Code:
``Evaluate(="=SUM(COUNTIF(Tableau[1].plage,{1,""1,*"",""*, 1"",""*, 1,*""}))")``
I should point out that I've never used evaluate with a table name as the range, although logically it should work the same as any normal range.

Replies
1
Views
64
Replies
0
Views
302
Replies
4
Views
78
Replies
3
Views
63
Replies
5
Views
77

1,128,157
Messages
5,629,023
Members
416,359
Latest member
Juena

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

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