Google Sheet CountIFs and Grouping

Question

I am looking to group Data dynamically through a formula in google sheets. I am not using PivotTables as I want to add additionally columns on the result table. Is there a way through a custom formula I will be able to achieve a grouped list in Column (E) from my source Data Column (A) which show the number of Y and N values for each Symbol in column (A). Please refer to image. I tried with a CountIFS, but had little luck. Any help would be appreciated.

Answer 1

Try =QUERY(A2:B11, "select A, count(A) group by A pivot B", 1) in E2

Reference:

• Sheet's QUERY

Edit 2: Answer with Formulae in every cell.

Use this formula in E3 and then drag to E3:F4 to count the number of Y & N for A & B.

=COUNTIFS($A$3:$A$11, $D3, $B$3:$B$11, E$2)

Use this formula in G3 and then drag to G3:G4

=IFERROR(E3/F3, E3)

This way you can filter the resulting table and sort it by any column.

If this works for you please mark this as the answer.