How to use Google Query to combine multiple rows into a single row with IF & SUM


We can get the following raw data from our project management software:

Month Project Billable Time
Jan 2022 Project 1 Yes 100
Jan 2022 Project 1 No 10
Feb 2022 Project 1 Yes 80
Feb 2022 Project 1 No 30
Jan 2022 Project 2 Yes 60
Jan 2022 Project 2 No 5
Feb 2022 Project 2 Yes 90
Feb 2022 Project 2 No 15

I need to change this data to the following format:

Month Project Billable Time Non-Billable Time Total Time
Jan 2022 Project 1 100 10 110
Feb 2022 Project 1 80 30 110
Jan 2022 Project 2 60 5 65
Feb 2022 Project 2 90 15 105

Putting the raw data into a Google Sheet I thought this might be possible using Google Query. I started with this:

=QUERY(dataRange,"SELECT Month,Project,SUM(Time) GROUP BY Month, Project")

But I can’t work out how to separate Billable & Non-Billable time & include this on a single row with the SUM of both times. Is this even possible using =QUERY?

If =QUERY can be used, what syntax should I use?

If =QUERY can’t be used, what method should I use instead?

>Solution :

Use the QUERY pivot clause like this (assuming your source table is in A1:D9 of your sheet):

=query(query({A1:D9},"select Col1,Col2,sum(Col4) group by Col1,Col2 pivot Col3 order by Col2",1),"select Col1,Col2,Col4,Col3,Col3+Col4 label Col3 'Billable time',Col4 'Non-billable time',Col3+Col4 'Total time'",1)

Leave a Reply Cancel reply