I think it depends on the kind and what you are seeking support with. CBT gets pushed the hardest and I do think it's helpful for a lot of people and worth trying in the first instance. Anecdotally, I found that people who generally have had good mental health throughout their lives and don't have 'complex' issues but suddenly start experiencing (even severe) anxiety and depression in adulthood seem to find it very helpful and even lifechanging. I think it's also a really good option for people who are looking for practical tips to get better and are put off by the 'airy fairy' parts of therapy.
Again, anecdotally, I have found that those who have complex trauma from childhood or who are neurodivergent, especially undiagnosed for years, often do not find it as useful. Unfortunately, the following of CBT is almost cult-like and there's a tendency to assume anyone who isn't helped by it is not engaging or trying hard enough, and I think that can be quite damaging.
I find in this country 'counselling' generally refers to person-centred counselling and the idea is that they don't give you advice, but help you figure things out yourself e.g. by asking questions, making connections, pointing out patterns and through unconditional positive regard. I think this can be very helpful especially if you don't have that from anyone else in your life, if you enjoy being introspective but you feel at a loss and directionless. I think this is especially helpful if you can't quite put your finger on what is wrong. Unfortunately, I think you need to be very motivated to keep going forward with this because it can be easy just to enjoy the validation and then stay stagnant. People who want the therapist to give them practical help in solving problems are likely to be very dissatisfied with this.
There are other specific modalities that are more suitable for people with complex trauma. I found Internal Family Systems to be lifechanging. EMDR is also supposed to be very good for trauma. DBT is another one focused on practical skills (developed for treating BPD) but is especially helpful for people with extreme emotions who often act in a way that isn't conducive to maintaining positive relationships. Something like psychodynamic therapy is really good for drilling down into how your childhood has made you who you are but can take a lot of time and a lot of people really aren't into what they feel is dwelling on their childhood.
So to answer your question, I have had counselling of different types throughout my life and found most of it very helpful. Person centred counselling helped me when I was stuck in a job I hated, not knowing where to go in life, not knowing how to deal with a relationship I felt dissatisfied with but was afraid of leaving. IFS and somatic therapy has helped me deal with a lot of issues that come from trauma I experienced from childhood and I realised a lot about why I was repeating the same patterns in my relationships, why I was people pleasing etc.
Especially privately, a lot of counsellors seem to take an integrative approach and are trained in various modalities, so as has already been said, I think the most important thing is the relationship with them, and being able to speak about what you feel is working and what is not.