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

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?

– ZygD
Dec 16, 2021 at 4:02
• @ZygD -- I can't share the actual sheet I'm working on, but made a very crude mock up of the situation: docs.google.com/spreadsheets/d/… In this sheet ranges 1-3 will always be a value of A in column B, 4-6 B, and 6-9 C. I'm trying to create a graph that displays the count of A B and C, broken up into stacks with a count of each instance of the numeric values in A. Dec 17, 2021 at 19:31

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.

• Thanks! What's the reference for the A2:B query here? Am I meant to point at an entire column? That doesn't seem to be working for me here. Dec 17, 2021 at 19:26
• Put the formula in cell `D1` in your sample spreadsheet. It will create a table with the unique values in column `A` as the first column and the counts by values A, B and C in three additional columns. Dec 17, 2021 at 23:00