How to shorten the formula

[prettysmart]

I have the following formula that I would like to be shortened:


=COUNTA(E169:O169)-COUNTIF(E169:O169,"=g*")-COUNTIF(E169:O169,"=p*")-COUNTIF(E169:O169,"=z*")-COUNTIF(E169:O169,"=????xdhs")


Can someone help me please? Besides, the formula will double deduct pepexdhs because falls into P* category as well as ????xdhs category. This makes the formula wrong. How can I resolve this problem?


Please give me advice. Thank you.

 

[SirJB7]

Hi, prettysmart!

Even I yet don't figure how to downsize it, I think that you've got a problem with the formula definition, as you say it rests twice a cells that begins with "g", "p" or "z" and that matches this format "????xdhs". There is a solution, but upsizing the formula length, will that be suitable for the task?

Regards!

 

[prettysmart]

Thanks SirJB7 for help. I upsize my formula by instead of doing "????xdhs", I include the first ? to "e???xdhs?", "h???xhds", and "m???xdhs" to exlcude "p???xdhs" to be counted twice. Is there any other way besides this?

 

[SirJB7]

Hi, prettysmart!

I now realize I forgot typing the formula, sorry. Here it is:

=COUNTA(E169:O169)-COUNTIF(E169:O169,"=g*")-COUNTIF(E169:O169,"=p*")-COUNTIF(E169:O169,"=z*")-COUNTIFS(E169:O169,"<>g*",E169:O169,"<>p*",E169:O169,"<>z*",E169:O169,"=????xdhs")

It's longer, I know, but it works. If I find a shorter method I'll write you.

Regards!

 

[SirJB7]

Hi, prettysmart!

I left your post open and answered without updating it so I didn't read your comment. However, the formula at my previous comment maintains the same spirit as your original question. If you want you can detail what structure might have every possible value in that range so as to think towards a better approach. Or upload a file, which will be better.

Regards!