CODEWITHSUNDEEP
google-sheetsspreadsheet
Carol.Kar
Carol.Kar

Reputation: 5345

Count occurrence by weekday in columns and row

I am having a google spreadsheet where the columns are specific dates and the rows are products.

| Weekdays  |        |     | 2        | 3        | 4        | 5        | 6        |
| Activity  | Streak | Max | 06.11.18 | 07.11.18 | 08.11.18 | 09.11.18 | 10.11.18 |
|-----------|--------|-----|----------|----------|----------|----------|----------|
| Product 1 | 0      | 5   | x        | x        | x        | x        | ...      |
| Product 2 | 0      | 6   | x        |          | x        | x        | ...      |
| Product 3 | 0      | 11  | x        | x        | x        | x        | ...      |
| Product 4 | 0      | 7   |          |          |          |          | ...      |

See my Data sheet.

On the tab Evaluation I tried to generate the following matrix, which should answer the question on which day of the week an occurrence of each of the products happened.

| Weekdays vs. Products | Product 1 | Product 2 | Product 3 | Product 4 |
|-----------------------|-----------|-----------|-----------|-----------|
| 1                     | #N/A      | #N/A      | #N/A      | #N/A      |
| 2                     | #N/A      | #N/A      | #N/A      | #N/A      |
| 3                     | #N/A      | #N/A      | #N/A      | #N/A      |
| 4                     | #N/A      | #N/A      | #N/A      | #N/A      |
| 5                     | #N/A      | #N/A      | #N/A      | #N/A      |
| 6                     | #N/A      | #N/A      | #N/A      | #N/A      |
| 7                     | #N/A      | #N/A      | #N/A      | #N/A      |

I tried simply a =COUNTIF(Data!$2:$2;$B3;C$2;Data!A3), however as you can see above I get #N/A.

Any suggestions how to count for each weekday the occurrence of the product?

Appreciate your reply!

PS.: Find my example google spreadsheet here: google spreadsheet

Upvotes: 0

Views: 211

Answers (1)

JPV
JPV

Reputation: 27242

Try

=countif(filter(filter(Data!$D$3:$BA;Data!$A$3:$A=C$13); Data!$D$1:$BA$1=$B14); "x")

and fill down and to the right as needed.

In Tabellenblatt3, I also added an alternitve approach, using a single formula. In cell B3 you'll find

=ArrayFormula(substitute(query(split(transpose(split(textjoin(;1;query(if(Data!D3:BA10="x";substitute(Data!A3:A10; " ";"/")&"_"&Data!D1:BA1&"_"&Data!D3:BA10;);;rows(Data!A3:A10)));" ")); "_"); "Select Col2, count(Col3) group by Col2 pivot Col1 label Col2 'Weekday'"); "/"; " "))

Note that this formula can break (because of the character limit in textjoin()) for large data sets.

Upvotes: 1

Related Questions