A Single Arrayformula to Sum a Filter of All Rows

Question:

This is a link to a simplified version of the spreadsheet I'm trying to build.  

Following is the screenshot of the sheet "Data": 

Following is the screenshot of the sheet "Summary":

On the Summary sheet, I have filter formulas in columns B and C to sum the quantities from the Data sheet that meet two criteria.

The real spreadsheet is more complicated, and I would like to reduce the number of formulas to speed it up and make it more efficient.  Can I replace the filter formulas with one arrayformula per column (or is there a better option)?

Solution:

Keep the first two rows of sheet "Summary" same as it is and put the following formula in the cell A3 of the sheet "Summary":

=ArrayFormula(QUERY(IF({1,0,0};'Data'!B2:B;IF({0,1,0};if(year('Data'!A2:A)=B2;if(month('Data'!A2:A)=B1;ROUND('Data'!H2:H;1);"");"");IF({0,0,1};if(year('Data'!A2:A)=C2;if(month('Data'!A2:A)=C1;ROUND('Data'!H2:H;1);"");""))));"select Col1, sum(Col2), sum(Col3) where Col1 <> '' group by Col1 order by Col1 label sum(Col2) '',sum(Col3) '' ";0))

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 or confidential projects:

http://igoogledrive.blogspot.in/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

Calculating working hours and payable amount per employee per month

Question:

( by vetobrian )

Please help!! I've created a form in google docs spreadsheets for employees to enter information into a daily time sheet record that is visible to administrative staff only once entered. The names entered into the form will not be fixed (we've got independent contractors across CA) but since the information will be recorded daily the spreadsheet will have multiple entries by any given employee per day for every calendar month. 

What I need to do is the following:

Match entries on "Sheet 4" Column A with the same names in column, total the number of hours & pay that are corresponding in Columns B & C, and re-list in 4 new Columns. All corresponding to each other, one by Name (representing only one name entry per month in new column #1), by dates (dates for one month all included in one cell & new row/cell for another month in new column #2, total hours calculated per month (in new column #3, and total pay calculated per month (in new column #4).

Example:

New Col #1.....New Column #2.....New Column #3.......New Column #4
Tom Hanks......9/2,9/3, 9,10,.......20hrs....................$300
Tom Hanks......8/1, 8/4. 8/30,......40hrs....................$600
Collin Hanks....9/4, 9/6, 9/30,.......100hrs..................$1,000
R.Williams.......6/4, 6/5, 6,15,.......25hrs....................$400
R.Williams.......9/1, 9/15, 9/21,.....80hrs....................$2,500



Solution:


Following is the screenshot of the Sheet named 'Form Response':

Now make a new Sheet 'SolutionSheet' in which you want to display the results:
Here is the screenshot of 'SolutionSheet':

To get the results as shown in the above screenshot, put the following formulas as in Cells mentioned below:

Put the following formula in Cell A2 of SolutionSheet:
=UNIQUE('Form Response'!B2:B)


Put the following formula in Cell B2 of SolutionSheet:
=concatenate(query('Form Response'!B2:C;"select C,', ' where B='" & A2 &"' label ', ' ', ' ";1))



Put the following formula in Cell C2 of SolutionSheet:
=sum(query('Form Response'!B2:I;"select I where B='" & A2 &"'";0))



Put the following formula in Cell D2 of SolutionSheet:
=sum(query('Form Response'!B2:J;"select I*J where B='" & A2 &"' label I*J ''";0))

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 or confidential projects:

http://igoogledrive.blogspot.in/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