Modify COUNTIF Formula to count inside parenthesis

[kdr121]

I have the following formula that I am using to count the number of occurrences of there being a value of 0 out of a number (i.e. 0/2 , 0/5, 0/1, etc...)

=COUNTIF(Z7:Z30,"0/*")

This works fine as long as the data in the above range (Z7:Z30) is formatted as the above i.e. 0/3, however there the way users will be putting data into this cell range going forward will have a set of
parenthesis around them - i.e. (2.00/3.00) but when the parenthesis is used the Formula is not counting the 0's records. So I believe I either need to have a formula in the cell data range that automatically removes the set of parenthesis as the users paste the data (with parenthesis) into Excel, or I need to adjust the formula above that is counting the 0/ records to essentially ignore the parenthesis.

I could not find an easy solution with the first option as I don't want to require my users to enter the data somewhere else for it to then reflect without the parenthesis in the cell range, so I was looking more at the 2nd option of modifying the above formula, however when I try to combine the above formula with a SUBSTITUTE formula to remove the parenthesis I am getting an error. This is what I tried to accomplish this:

=COUNTIF(SUBSTITUTE(SUBSTITUTE(Z7:Z30, "(", ""), ")", ""),"0/*")

This formula works appropriately IF the data is formatted without the parenthesis, however once the parenthesis is put around the data then it does not work:

Below I am using the SUBSTITUTE formula to remove the parenthesis from cell Z29 above it. I either want to be able to utilize this formula in the actual cell range that the users are inputting the data into and have it re-format to remove the parenthesis, OR I need to be able to combine the below formula into the COUNTIF formula above (prefer whatever the simplest solution is).

Hope I have explained this well enough but please let me know if I can clarify anything, thanks in advance!

 

[FormR]

Hi, welcome to the forum!

Can you change your COUNTIF() to:

=COUNTIF(Z7:Z30,"(0/*")

Or maybe even.

=COUNTIF(Z7:Z30,"*0/*")

 

[Fluff]

Hi & welcome to MrExcel.
will a zero value be entered as (0/2.00) or (0.00/2.00) or something else?

 

[kdr121]

Hi, welcome to the forum!

Can you change your COUNTIF() to:

=COUNTIF(Z7:Z30,"(0/*")

Or maybe even.

=COUNTIF(Z7:Z30,"*0/*")

Hi there! I tried both formulas and the first one results in 0 which is not correct , while the 2nd formula is showing 6 but it is counting the non-zero records such as (1.00/2.00) so this is also incorrect.

 

[kdr121]

Hi & welcome to MrExcel.
will a zero value be entered as (0/2.00) or (0.00/2.00) or something else?

Good question, I would expect it to have the decimals so it would be 0.00

 

[Fluff]

In that case try

=COUNTIF(Z7:Z30,"(0.00/*")

 

[kdr121]

Hi there! I tried both formulas and the first one results in 0 which is not correct , while the 2nd formula is showing 6 but it is counting the non-zero records such as (1.00/2.00) so this is also incorrect.

What about using this?

=COUNTIF(Z7:Z30,"*(0.00/*")

Is it possible to use this with 0/* also, to catch any that could be entered either as (0.00/xxx) or (0/x)...

 

[Fluff]

How about

=SUM(COUNTIF(Z7:Z30,{"(0.00/*","(0/*"}))