How to calculate weekly sum (sum of last 7 rows printed in 7th row) based on daily values, same question for monthly (based on weekly or daily). I have tried some formulas but they all failed.
如何计算每周和每月(根据每周或每天)相同的问题，计算每周的和(最后7行的总和)。我试过一些公式，但都失败了。
(first column is date column, not in image)
(第一列是日期列，不是图像)
P.S. sorry for "too localized" and "not constructive" I just don't know how to ask this differently
对不起，“太本地化”和“没有建设性”，我只是不知道该怎么问。
2 个解决方案
#1
8
For the week column, you can add a formula like =IF(WEEKDAY(A8)=7,sum(B2:B8),"") (to put the sum of the week on Sundays.
在一周的专栏中，你可以添加一个公式，比如:IF(工作日，A8)=7，和(B2:B8)，“”)(将周日的时间加起来。
For the month column: =IF(MONTH(A2)<>MONTH(A2+1),SUM(B$2:B2)-SUMIF(A$2:A2,"<"&DATE(YEAR(A2),MONTH(A2),1),B$2:B2),"")
月柱:=如果(月(A2)< >月(A2 + 1),和(B 2美元:B2)条件求和(美元2:A2,“<”和日期(年(A2)月(A2),1),B 2美元:B2),“”)
#2
2
For weekly column
每周列
=IF(IFERROR(WEEKNUM(A2)=WEEKNUM(A1), FALSE), "",SUM(B2:B8))
for monthly column
每月列
=IF(IFERROR(MONTH(A2)=MONTH(A1), FALSE), "",SUMIF(A2:A33, "<"&DATE(YEAR(A2),MONTH(A2)+1,1), B2:B33))
these should work anywhere. Week & month sums will be listed on first day of week/month (what your sheet seemed to have)
这些应该在任何地方工作。周和月的金额将在周/月的第一天列出(你的表似乎有)
Note: the ranges in sumif are 31 days long. I realize every month isnt 31 days. But they are AT MOST 31 days, and each day will only be included if it's month is correct, so it holds for 28,29, and 30 day months as well. This means the formula WILL work for all months without modification.
注:sumif的范围为31天。我知道每个月都是31天。但是它们最多是31天，并且只有在月份是正确的情况下才会包括每天，所以它也可以持续28天、29天和30天。这意味着该公式将在没有修改的情况下适用所有月。
#1
8
For the week column, you can add a formula like =IF(WEEKDAY(A8)=7,sum(B2:B8),"") (to put the sum of the week on Sundays.
在一周的专栏中，你可以添加一个公式，比如:IF(工作日，A8)=7，和(B2:B8)，“”)(将周日的时间加起来。
For the month column: =IF(MONTH(A2)<>MONTH(A2+1),SUM(B$2:B2)-SUMIF(A$2:A2,"<"&DATE(YEAR(A2),MONTH(A2),1),B$2:B2),"")
月柱:=如果(月(A2)< >月(A2 + 1),和(B 2美元:B2)条件求和(美元2:A2,“<”和日期(年(A2)月(A2),1),B 2美元:B2),“”)
#2
2
For weekly column
每周列
=IF(IFERROR(WEEKNUM(A2)=WEEKNUM(A1), FALSE), "",SUM(B2:B8))
for monthly column
每月列
=IF(IFERROR(MONTH(A2)=MONTH(A1), FALSE), "",SUMIF(A2:A33, "<"&DATE(YEAR(A2),MONTH(A2)+1,1), B2:B33))
these should work anywhere. Week & month sums will be listed on first day of week/month (what your sheet seemed to have)
这些应该在任何地方工作。周和月的金额将在周/月的第一天列出(你的表似乎有)
Note: the ranges in sumif are 31 days long. I realize every month isnt 31 days. But they are AT MOST 31 days, and each day will only be included if it's month is correct, so it holds for 28,29, and 30 day months as well. This means the formula WILL work for all months without modification.
注:sumif的范围为31天。我知道每个月都是31天。但是它们最多是31天，并且只有在月份是正确的情况下才会包括每天，所以它也可以持续28天、29天和30天。这意味着该公式将在没有修改的情况下适用所有月。