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 Final Grades With Weighted Marks

Question:

I'm working on an assignment for my class and the question is..

11. Using a formula, calculate the final grades as a whole number out of 

100 (not a percentage).  The final grade should not have decimal places.  

Use the round function to accomplish this, rather then a formatting 

option. Use the following grading scheme 

a. Assignment 1 - 10% 

b. Assignment 2 – 15%

c. Assignment 3 – 20% 

d. Quizzes (Best 7) – 20% 

e. Final Exam – 35%

Use absolute cell referencing of the maximum marks located at the 

top of each column in the spreadsheet. DO NOT hard code maximum 

marks into the formula. 

So I need to know what the formula is that I should be using is?

Solution:

Make a sheet which will have Column headers in Row 1, 'Total Marks' of assignment in Row 2, Weight of each Assignment in Row 3, and Marks obtained in Row 4.
Have a look at the screenshot below:

Put the following formula in Cell D8:
=ROUND(((B4*B3)+(C4*C3)+(D4*D3)+(E4*E3)+(F4*F3))/((B2*B3)+(C2*C3)+(D2*D3)+(E2*E3)+(F2*F3))*100)

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