英文:
Sum up horizontal drop-downs with text options
问题
我有一个包含员工姓名和每月日期的表格。每天都有一个下拉列表，其中包含选项如“办公室”，“休假”，“远程工作”等。我的目标是对每个选项进行日期总和。
我还有另一个包含选项名称和它们的数值的工作表。
我用于AH2的公式是：SUMIF(C2:AG2, Locations!A2, Locations!C2)，它对前三个下拉列表（列C、D、E）有效。更改列F:AG中的选项不会更新相应的总数。
如何修复这个问题？
英文:
I have a table that contains staff names and days of month. Each day has a drop-down list with options like "Office", "Vacation", "Remote work" etc. My goal is summing up days for every option
I have also another sheet with option names and their numeric values
The formula I use for AH2: SUMIF(C2:AG2, Locations!A2, Locations!C2) works good but only for first 3 dropdows (columns C, D, E). Changing options in columns F:AG won't update corresponding totals
How to fix that?
答案1
得分: 2
如果您使用MS365或Excel 2021，可以使用XLOOKUP()函数来提取每个Location Code的Increment Value，然后使用COUNTIF()函数相乘以获得所需的输出。
在上述XLOOKUP()函数的公式中，我使用了通配符匹配模式，因为根据您的截图，AI1单元格只显示为Remote，而在其他表中显示为Remote Work。
此外，如果您使用LET()函数，我们可以避免在公式中重复使用范围，并且更易于阅读。
如果您不使用MS365或Excel 2021，那么您可以尝试使用SUMIFS() + COUNTIFS()与INDEX()和MATCH()。
另一种替代方法是使用FILTER()和COUNTIFS()。
在上述所有公式方法中，需要根据需要向下和向右填充，并根据您的情况更改范围。
英文:
If you have MS365 or Excel 2021, you can use XLOOKUP() to extract the Increment Value for each Location Code and then multiply it with a COUNTIF() function to get the desired output.
• Formula used in cell AH2
=XLOOKUP(" "&AH$1&" *"," "&$B$6:$B$8&" ",$C$6:$C$8,,2)*
COUNTIF(C2:AG2,XLOOKUP(" "&AH$1&" *"," "&$B$6:$B$8&" ",$A$6:$A$8,,2))
In the above formula for XLOOKUP() I have used wild card for match mode since as per your screenshots, the AI1 cell shows only Remote while in other sheet it shows as Remote Work
Also if you use the LET() function then we can avoid repeating the ranges in the formula and makes easier to read.
=LET(x,$C$6:$C$8,y,$A$6:$A$8,z,$B$6:$B$8,
XLOOKUP(" "&AH$1&" *"," "&z&" ",x,,2)*
COUNTIF(C2:AG2,XLOOKUP(" "&AH$1&" *"," "&z&" ",y,,2)))
If you are not using MS365 or Excel 2021 then you could try using it with SUMIFS() + COUNTIFS() with INDEX() & MATCH()
• Formula used in cell AH2
=SUMIFS($C$6:$C$8,$B$6:$B$8,AH$1&"*")*
COUNTIFS($C2:$AG2,INDEX($A$6:$A$8,MATCH(" "&AH$1&" *"," "&$B$6:$B$8&" ",0)))
Another alternative using FILTER() & COUNTIFS()
=LET(x,FILTER(HSTACK($A$6:$A$8,$C$6:$C$8),ISNUMBER(SEARCH(AH$1,$B$6:$B$8))),
y,COUNTIFS($C2:$AG2,TAKE(x,,1)),
SUM(y*TAKE(x,,-1)))
Instead of COUNTIFS() in the above formula using BOOLEAN Logic
=LET(x,FILTER(HSTACK($A$6:$A$8,$C$6:$C$8),ISNUMBER(SEARCH(AH$1,$B$6:$B$8))),
y,SUM(--($C2:$AG2=TAKE(x,,1))),SUM(y*TAKE(x,,-1)))
All the formulas approaches shown above needs to be filled down and filled right accordingly, also do change the ranges as per your suit.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论