count the number of yes and no for month & category in Google Sheet

0
votes

I want help in re-displaying the data ( count the number of yes and no for month and category ) on Sheet 2 by code (automatically)

Example:

https://docs.google.com/spreadsheets/d/14idoHEq1S__TJkpa07R86mCLhKx5i-FMcO5ZQZ8PWOo/edit?usp=sharing

Sheet1

enter image description here

To Sheet 2

enter image description here

2
google-sheetsgoogle-query-language
It seems that you want to count the number of yes and no's for each month for each quarter, is that correct? If so, please write and describe that in your question – Asad-ullah Khan
Feel free to accept and upvote the answer below if you feel it was useful to you – NaziA

2 Answers

0
votes

If you only want to count the number of occurrence of a certain value and you know the range of cells, you could use =COUNTIF(range, condition) formula to your cells and to reference from different Sheet, use =[SheetName]![cell].

Example:

Sheet1

Sheet1

To count the number of "Yes" in Sheet1, I used =COUNTIF(Sheet1!B2:B9, "Yes"). Sheet2

enter image description here

0
votes

use:

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(IF('Sheet 1'!B2:F="",,
 MONTH('Sheet 1'!A2:A)&TEXT('Sheet 1'!A2:A, "mmmm")&"♦"&
 'Sheet 1'!B1:F1&"♦"&'Sheet 1'!B2:F&"♦"&'Sheet 1'!B2:F)), "♦"), 
 "select Col1,Col2,count(Col3)
  where Col1 is not null 
  group by Col1,Col2
  pivot Col4
  label Col1'Month',Col2'ZONE' 
  format Col1'mmmm'"))

enter image description here