I have a spreadsheet that looks like this:
| Dept | Process | Job No | Job Date | Job Time |
|---|---|---|---|---|
| a | x | 123 | 18/7/22 | 12:00 |
| a | w | 123 | 18/7/22 | 13:00 |
| a | y | 123 | 19/7/22 | 10:00 |
| b | z | 456 | 18/7/22 | 14:00 |
and a formula from ExtendOffice
=ArrayFormula(QUERY(A1:A&{"",""},"select Col1, count(Col2) where Col1 != '' group by Col1 label count(Col2) 'Count'",1))
which gives
| Dept | Count |
|---|---|
| a | 3 |
| b | 1 |
As the formula counts the number of occurrences of a value in only one column, how should I modify it to include multiple columns? Say, including Job Date?
| Job Date | Dept | Count |
|---|---|---|
| 18/7/22 | a | 2 |
| 18/7/22 | b | 1 |
| 19/7/22 | a | 1 |
Thank you!
>Solution :
Try below QUERY() formula-
=QUERY(A1:E5,"select D, A, Count(A) group by D, A label Count(A) 'Count'",1)
