0

I have data as follows:

Name | Value | Y/N | Value | Y/N
A    | 10    |  Y  | 12    |  N
B    | 100   |  Y  | 120   |  N 
A    | 20    |  Y  | 10    |  Y
B    | 200   |  N  | 120   |  Y
C    | 1000  |  Y  | 12    |  Y

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.

0

=QUERY(QUERY(QUERY(
 {QUERY(Sheet1!A2:E, "select A, count(C) where C='Y' group by A label count(C)''", 0),
  QUERY(Sheet1!A2:E, "select A, count(E) where E='Y' group by A label count(E)''", 0)},
 "select *", 0), 
 "select Col1, sum(Col2)+sum(Col4) group by Col1 label sum(Col2)+sum(Col4)'COUNT'", 0),
 "select Col1, Col2 label Col1'Name'")

1

  • Thank you for this answer but as I mentioned there are many columns and cannot hard-code it for all columns in my spreadsheet. The example is a simple sample of my data. – user1527152 Jan 14 at 22:23
  • @user1527152 well this is the way. put those many columns in array {} and then sum them on 5th row like sum(Col2)+sum(Col4)+sum(Col6)+sum(Col8).... – MARK MY ANSWER Jan 14 at 22:30
  • I have 100+ columns meaning I would have to write QUERY(...) that many time – user1527152 Jan 14 at 23:00
  • @user1527152 go for it :) unfortunately there isnt better solution (unless you will write yourself custom script) – MARK MY ANSWER Jan 14 at 23:05
0

So I finally found how to do it.

I first grouped by column via the formula =COUNTIF(Sheet1!1:1, "Y") (it needs to be expended to the range desired)

which result in (on the original example):

Name | COUNT
A    | 1
B    | 1
A    | 2
B    | 1
C    | 2

Note: to also have the column name just do =A:A

Then, I used a pivot table and added the column Name and Count

Note: it also works with =QUERY(A2:B1000,"select A, count(B) group by A")

  • 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 Gubala Jan 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). – user1527152 Jan 16 at 13:56

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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