Count same text string from multiple columns

[robinsab]

Hello,

I have a table with mutiple columns containing the same menu of text values. I want to count the total across all of these columns of how many times each value is has been entered. These values are reasons for not checking a certain box within a form so all the rows are a different date of a submission and each check box has its own column that is either blank or contains one of the values from the text options. So basically I want to know how many times (across all check boxes and all submissions) that each exuse for not checking the box has been used. I have tried mannnny different combination of demands and cant seem to get it to count correctly. Any help is really appreciated!

Thank you!

[Anonymous · Accepted solution]

Hi @robinsab ,

Based on your description, it is possible that your goal can be achieved with a simple data transformation, and I have created the following data:

Option A、Option B、Option C  for your checkbox and Fine、Bad、Pretty for your checkbox answer.

First, unpivot Option A、Option B、Option C  three columns like this:

Then group by Value:

Please feel free to correct me and provide more information if I have misunderstood you!

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@robinsab So typically you would unpivot those columns and then it becomes a trivial problem to solve. Otherwise, you need to use something like MC Aggregations: Multi-Column Aggregations (MC Aggregations) - Microsoft Fabric Community