Nesting the Count if formula

[Deuce Bucksman]

Hello, Gurus.

I'm trying to count only cells that are equal to 1 in a string of 8 cells (fifth cell to the right). For some reason, the count if formula is not allowing me to nest the left and right functions together. Can anyone tell me what is wrong with the formula below. I really appreciate your help! Thanks!


countif(C19:C338,LEFT(RIGHT(C19,4),1) "=1")

 

[jasonb75]

Don't know what you're trying to do with"=1" at the end, but it looks like that is the cause of the problem.

Try removing that, if it doesn't give the result you want then try posting some examples.

 

[Deuce Bucksman]

The left(right) nesting formula returns the value of 1. I put the "= 1" so it would only count the cells where the nested formula is equal to 1. Otherwise it will just return the value of zero since i'm not telling it to equal anything. I am perplexed.

 

[yamasaki4]

The knowledge that very attractive.

 

[MrKowz]

You can't use additional functions inside of a COUNTIF, as it is not evaluated as an array formula. For your requirements, try a SUMPRODUCT:

=SUMPRODUCT(--(LEFT(RIGHT(C19:C338,4),1)="1"))

 

[barry houdini]

can you give examples of the values you want to count - are these numbers or alphanumeric or something else?

 

[jasonb75]

Or just use

=COUNTIF($B$5:$B$19,"????1???")

 

[barry houdini]

That was the reason for my question, Jason - you can't use wildcards with numeric data......

 

[jasonb75]

I guessed that barry, but didn't see your question til after I hit the submit button.

Failure to refresh?? Gulity as charged lol

 

[Deuce Bucksman]

Thanks, all! Both the sumproduct formula and the countif formula worked perfectly. To answer Houdini's question: The cells to be evaluated are text ( I think) since it is both numeric and text (84xx1x1B). Sorry it took so long to come back with a response. I appreciate all your help!