0

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?

2
  • 1
    A copy of your spreadsheet would be helpful.
    – 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.
    – Andrew Kim
    Dec 17, 2021 at 19:31

1 Answer 1

0

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.

2
  • 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.
    – Andrew Kim
    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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.