Is there a way to track the frequency of a relationship between two columns?

Question

I have two columns in a spread sheet where column A tracks about 100 different categories. In Column B, I have a formula that maps specific categories in Column A to one of 10 separate categories in Column B.

These are all non-numerical values, and I'd like to track the frequency of Rows of Categories in Column B, with each bar in the graph breaking up into a stacked graph representing the frequency of recurrences of Column A.

Is this possible?

Answer 1

Use query() to pivot the data into as many columns as there are unique categories in column B, like this:

=query( 
  A2:B, 
  "select A, count(A) 
   where B is not null 
   group by A 
   pivot B", 
  0 
)

Then use the result table to insert a chart that has ten stacked bars.