Google Spreadsheet Advanced Query

Question:

I am trying to figure out a solution for a particular problem. I am using a calculation posted on this forum to count only once for multiple same date entries, however I also want these counted by a value in another column. For example, If there are multiple entries for the same date, but the column next to one entry is "blue" and another entry for the same date is "red", each of these is given its own count. I used the formula from this forum:
=Query(index(if({1,1},int('Sheet1'!A:A))),"select Col1,count(Col2) where Col1>1899 group by Col1 ",0)
To count the total number of unique days, but tried to modify it to be able to determine how many days I worked on "blue" or "red" regardless of the number of entries for the same day. I can share the spreadsheet I am working on if it would be helpful.

====


I have tried a number of possible solutions, but am unable to get this to work. Any help would be greatly appreciated.

I tried to modify the equation to only return those days that I had entered work for a specific project work-type. For example, if I create multiple entries on the same date for "Technology Rework" on the first page, I want to show that only one day was spent working on that portion. If I include another date entry for "Technology Rework" it would calculate the total number of days worked as 2 days. So I don't want to count more than once for a single date, but I want that time to be added to the number of total days worked on that area of the project.
I have included sample data in the following link:



The equation is in field I6 on sheets "Chat Page Rework" and "Technology Rework"

Please let me know if there is any further information I can provide.

Thank you for your help,
Kyle

Solution:

Try the following formulas one by one:

=query(Sheet1!A4:C;"select C,A,sum(B) where C='Chat Page Rework' group by C,A label A '', sum(B) ''";0)

=query(query(Sheet1!A4:C;"select C,A,sum(B) where C='Chat Page Rework' group by C,A label A '', sum(B) ''";0);"select Col1,count(Col2),sum(Col3) group by Col1 label Col1 '',count(Col2) '',sum(Col3) ''")

=query(query(query(Sheet1!A4:C;"select C,A,sum(B) where C='Chat Page Rework' group by C,A label A '', sum(B) ''";0);"select Col1,count(Col2),sum(Col3) group by Col1 label Col1 '',count(Col2) '',sum(Col3) ''");"select Col2,Col3")

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html

Thanks,

Kishan.

iGoogleDrive