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 !
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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,*"}))
 
Upvote 0
Solution
How about this ? Ctrl +Shift+enter
{=COUNT(SEARCH(1,Tableau[1].plage))}
 
Upvote 0
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 !
 
Upvote 0
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.
 
Upvote 0
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 !
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

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
Back
Top