google sheets – Count cells across columns based on one column

Try this formula. I created a new sheet to view the results – that’s why the ranges reference “Sheet1”.

=query({Sheet1!A2:C21;Sheet1!A2:A21,Sheet1!D2:E21;Sheet1!A2:A21,Sheet1!F2:G21},"select Col1,Col2,Col3,COUNT(Col3) where Col2 <>'' group by Col1,Col2,Col3 label COUNT(Col3) ''")


Results

(Note: the header line is entered manually)

Screenshot