Question on Countif

faram

New Member
Joined
Jan 21, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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 !
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,490
Office Version
  1. 365
Platform
  1. Windows
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,*"}))
 
Solution

alz

Board Regular
Joined
Jul 17, 2020
Messages
130
Office Version
  1. 2019
Platform
  1. Windows
How about this ? Ctrl +Shift+enter
{=COUNT(SEARCH(1,Tableau[1].plage))}
 

faram

New Member
Joined
Jan 21, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Dec 30, 2008
Messages
12,490
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 21, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Dec 30, 2008
Messages
12,490
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
12,490
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,015
Messages
5,628,146
Members
416,294
Latest member
McStuffins

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