How can I separate a column into multiple columns based on values?

Question

I have searched on a lot of pages but I cannot find a solution to my problem except in reverse order. I have simplified what I do, but I have a query that comes looking for information in my data sheet. Here there are 3 columns, the date, the amount and the source.

I would like, with a query function, to be able to make different columns which counts the information of column C based on the values of its cells per month, like this

I'm okay with the start of the formula

=QUERY(A2:C,"select month(A)+1, sum(B), count(C) where A is not null group by month(A)+1")

But as soon as I try a little different things by putting 2 query together in an arrayformula, obviously the row count doesn't match as some minus are 0 for some sources.

Do you have a solution for what I'm trying to do? Thank you in advance :)

Answer 1

try:

=INDEX({
 QUERY({MONTH(A2:A), B2:C}, 
 "select Col1,sum(Col2) where Col2 is not null group by Col1 label Col1'month',sum(Col2)'amount'"), 
 QUERY({MONTH(A2:A), B2:C, C2:C}, 
 "select count(Col3) where Col2 is not null group by Col1 pivot Col4")})

Answer 2

Solution:

It's not possible in Google Query Language to have a single query statement that has one result grouped by one column and another result grouped by another.

The first two columns can be like this:

=QUERY(A2:C,"select month(A)+1, sum(B) where A is not null group by month(A)+1 label month(A)+1 'Month', sum(B) 'Amount'")

To create the column labels for the succeeding columns, use in the first row, in my example, I1:

=TRANSPOSE(UNIQUE(C2:C))

Then from cell I2, enter this:

=COUNTIFS(arrayformula(month($A$2:$A)),$G2,$C$2:$C,I$1)

Then drag horizontally and vertically to apply to the entire table.

Results: