Tuesday, July 2, 2013

Google Spreadsheet date format as a day of the week

Question:


I have a list of date, value,etc and on a separate sheet i have to add all the values for Mondays.


Solution:

Following is the screenshot of the data:


As you can see in the above screenshot, value of Cell F6 is "MONDAY"

Now put the following formula in Cell G6:

=sum(iferror(filter(D2:D;CHOOSE(WEEKDAY(A2:A);"SUNDAY";"MONDAY";"TUESDAY";"WEDNESDAY";"THURSDAY";"FRIDAY";"SATURDAY")=F6;B2:B="Session-1";C2:C="C")))

the above formula will give you the total amount from Column D, where Column A is a "MONDAY", Column B is "Session-1" and Column C is "C".

Now you can drag the above formula up to Cell G12.

Similarly, put the following formula in Cell H6:

=sum(iferror(filter(D2:D;CHOOSE(WEEKDAY(A2:A);"SUNDAY";"MONDAY";"TUESDAY";"WEDNESDAY";"THURSDAY";"FRIDAY";"SATURDAY")=F6;B2:B="Session-2";C2:C="C")))


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:

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,

No comments:

Post a Comment