And I would like to have a condensed view of this data which would look like this:

Name | COUNT
A | 3
B | 2
C | 2

I found how to do it for one column with the QUERY function:

=QUERY(Sheet1! A2:C1000,"select A, count(C) where C='Y' group by A")

However, I don't know how to repeat this for all the columns in my spreadsheet.
The example is simple and the data spreads both in rows in columns (in the same pattern), so I cannot hard-code it for the column.

Maybe I would have to do that in two steps, I'm looking for a suggestion or idea how to do that.

If this was your solution please mark your answer and explain how it performs the task better than suggested answers (we are looking through a microscope at a small sample of what you are trying to do)
– Tyler GubalaJan 15 at 20:26

I can't mark within 24h. The question specifies that the solution should be for an arbitrary number of columns and rows. This is the only solution doing so (atm).
– user1527152Jan 16 at 13:56