Monday, February 25, 2013

yogi_Compute Sum Of Amounts For A Specified Week Number Based On Whether Week Begins With Sunday Or Monday


                                          Google Spreadsheet   Post  #1044
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 25, 2013
user Eduard Grama said:(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/ysegyRWPYJY)
Hi,

I am currently using Excel 2013 Professional but I want to be able to use the same formula in a Google Spreadsheet. Basically, the formula I have in Excel is this:

=SUMPRODUCT(--(INT((Sheet1!$U$2:Sheet1!$U$1500-DATE(YEAR(Sheet1!$U$2:Sheet1!$U$1500),1,1)-WEEKDAY(Sheet1!$U$2:Sheet1!$U$1500))/7)+2=H1),Sheet1!$AH$2:Sheet1!$AH$1500)

Column U represents dates.
Cell H1 is the number of the week (1-53) and column AH is the amount invoiced. 
The formula returns me the sum of all invoices for the week number specified in cell H1. All other invoices that don't have a date within the week number specified in cell H1 are neglected. 

Thank you.
Eduard
---
https://docs.google.com/spreadsheet/ccc?key=0Amiza5hKfLtzdEV4TE93MjZxM0pHV3dWd3Rac0l1SGc&usp=sharing

Thank you so much for your help Yogi.

Eduard
------------------------------------------------------------------------------------------------------------------------
in the following I have presented a solution to a bit more generalized problem