Grouping Multiple Data to Output Common Values in Google Sheets

Question

Apologies if this has been covered before...

I'm creating a Google Sheet spreadsheet to group the amount of occurrences of a particular product, based on the country that it is assigned to. Imagine at first we have three products; A, B and C. My table might look like this:

What I'd like to find is a formula that would automatically update as more countries were added, or if more products were sold into a country... the resulting table it would generate would look something like this:

I've been experimenting with various ArrayFormulae, and in particular this one:

"=ArrayFormula(QUERY(TRANSPOSE(SPLIT(JOIN(" ",'Feedback Responses'!B2:B65)," ")&{"";""}),"select Col1, count(Col2) where not(Col1 matches 'and|or') group by Col1 order by count(Col2) desc limit 10 label Col1 'Word', count(Col2) 'Frequency'",0))"

But that merely generates me keywords which can separate out spaces in countries like New Zealand. It also doesn't account for 3 or more products...

Answer 1

The data summary that you are trying to build, some people call them "crosstab report" or "pivot table".

Google Sheets, have at least two easier than the complex formula in the question ways:

1. QUERY function using the GROUP BY and PIVOT keywords. See QUERY for details.
2. Pivot Table report. See How to create a pivot table report for details.

Answer 2

may be you're looking for simple query formula:

=query({Sheet1!A:C},"select Col1, Count(Col3) where Col1 <> '' group by Col1 pivot Col2",1)

I used Col3 and range A:C here in order to be able to pivot column B (Products).

---

More info here.