在表格中，我需要总结从周三开始到周二结束的每个工作周，并在其中的任何一天多次支付工资
对始终为每周周三到周二的发薪周求和，其中在该周内的任何一天多次付款，即付款可能仅在周四、周六和周日。下周可能是周三、周五和周二。因此，付款的星期几会有所不同，该周的付款次数也会有所不同（一次、两次、三次等）。 我需要像 weeknum 这样的东西，可以选择一周开始的那一天（星期三）。如果需要，可以添加辅助列。
4835259 Mon 3/21 CINCINNATI $8,000
5245716 Tue 3/22 HIGHLAND IL $2,500
5352002 Thu 3/24 LOUISVILLE $4,475
5352016 Fri 3/25 NASHVILLE $3,375
所以在这个例子中，我只想要 4475 美元和 4475 美元。合计 3375 美元。每周的最近几天周三到周二（不包括上周二）。正如您所看到的，不一定要为一周中的每一天输入数据。所以仅仅倒数7天是不行的。最近一周的总计可能有 4 或 5 个条目要总计，或者只有 1 或 2 个条目。每周周三到周二总计，不包括上周二。
下面的作品，但没有固定范围。或者计算星期二但只保留最近的星期二以消除上星期二。
=SUMPRODUCT(F421:F423, (B421:B423="星期三" )+(B421:B423="周四")+(B421:B423="周五")+(B421:B423="周六")+(B421:B423="周日")+(B421:B423="周一" )+(B421:B423="星期二"))
Sum a payweek that is always Wednesday through Tuesday each week where the payments are made multiple times throughout that week on any day in that week, i.e., payments might be Thursday, Saturday, and Sunday only. Next week might be Wednesday,Friday, and Tuesday. So the day of the week of payments vary as do the number of payments in that week (once, twice, three times, etc).
I need something like weeknum where the day the week the week starts can be chosen (Wednesday). Can add a helper column if needed.
4835259 Mon 3/21 CINCINNATI $8,000
5245716 Tue 3/22 HIGHLAND IL $2,500
5352002 Thu 3/24 LOUISVILLE $4,475
5352016 Fri 3/25 NASHVILLE $3,375
So in this example, I only want $4475 & $3375 summed. Most recent days of week Wednesday through Tuesday (Never including last Tuesday). As you can see, data is not necessarily entered for every day of the week. So just counting back seven days won't work. The most recent week to be totalled might have 4 or 5 entries to sum, or only 1 or 2. Sum each week Wednesday through Tuesday not including last Tuesday.
Below works, but w/o the fixed range. Or count Tuesday but only keep the most recent Tuesday to eliminate last Tuesday.
=SUMPRODUCT(F421:F423, (B421:B423="Wed"
)+(B421:B423="thu")+(B421:B423="fri")+(B421:B423="sat")+(B421:B423="sun")+(B421:B423="mon")+(B421:B423="tue"))
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）