=COUNTIFS in column with grouping...

[RobbieC]

Hi there, I have a column of values G1:G35 and I want to create a column next to it H1:H35 with a COUNT of each value. I can get this to work with the code in H1 and dragged down to H35:

=COUNTIFS($G$1:G1,G1,$G$1:G1,"<>0",$G$1:G1,"<>""",$G$1:G1,"<>U100")

This misses out 0's, nulls and U100's - this is working fine!

However, how can I group values together in this formula?

For example, if the value is SPT or CPT, they are counted as the same.

If you can point me in the right direction, that'd be brilliant. Thanks

 

[RobbieC]

Would it be something along the line of

=COUNTIFS($G$1:G1,{"SPT","CPT"}[COLOR=#333333],$G$1:G1,"<>0",$G$1:G1,"<>""",$G$1:G1,"<>U100"[/COLOR])

 

[RobbieC]

Anyone? If it can't be done with COUNTIFS, would there be another workaround that I could use?

 

[RobbieC]

I'm going crazy trying to find a solution to this.... surely there must be a way to do this???