英文:
SUMPRODUCT Formula is throwing an error Formula Parse Error
问题
我已经创建了以下公式，而不是使用 SUMIFS，因为数组在 Google Sheets 的 SUMIFS 函数中不起作用，所以我使用了 Google 制作了这个公式，但我遇到了一个错误 Formula Parse Error。
你的帮助将不胜感激。
=SUMPRODUCT(('3. Emp. Leave Setup'!B:B=B2:B)*('3. Emp. Leave Setup'!F:F>=DATEVALUE('Yearly Clndr'!E2:E))*('3. Emp. Leave Setup'!G:G<=DATEVALUE('Yearly Clndr'!F2:F)),'3. Emp. Leave Setup'!J:J*0)
以下是可以正常工作的 SUMIFS 公式，但无法将其转换为数组：
=SUMIFS('3. Emp. Leave Setup'!J:J,'3. Emp. Leave Setup'!B:B,B2,'3. Emp. Leave Setup'!F:F,">="&'Yearly Clndr'!E2,'3. Emp. Leave Setup'!G:G,"<="&'Yearly Clndr'!F2)
英文:
I have created below formula instead of SUMIFS because arrays do not work with Google sheets SUMIFS function so i used google to make this fomrula but i am getting an error Formula Parse Error
Your help will be much appreciated.
=SUMPRODUCT(('3. Emp. Leave Setup'!B:B=B2:B)*('3. Emp. Leave Setup'!F:F>=DATEVALUE('Yearly Clndr'!E2:E))*('3. Emp. Leave Setup'!G:G<=DATEVALUE('Yearly Clndr'!F2:F)),'3. Emp. Leave Setup'!J:J*0)
Here is SUMIFS formula which is working fine but unable to convert it into Arrays
=SUMIFS('3. Emp. Leave Setup'!J:J,'3. Emp. Leave Setup'!B:B,B2,'3. Emp. Leave Setup'!F:F,">="&'Yearly Clndr'!E2,'3. Emp. Leave Setup'!G:G,"<="&'Yearly Clndr'!F2)
答案1
得分: 1
为了获得逐行结果，每个值在范围B2:B10中产生一个结果，可以使用map()，如下所示：
=map(
B2:B10,
lambda(
value,
sumifs(
'3. Emp. Leave Setup'!J:J,
'3. Emp. Leave Setup'!B:B, value,
'3. Emp. Leave Setup'!F:F, ">=" & 'Yearly Clndr'!E2,
'3. Emp. Leave Setup'!G:G, "<=" & 'Yearly Clndr'!F2
)
)
)
英文:
To get row-by-row results, one result per each value in the range B2:B10, use map(), like this:
=map(
B2:B10,
lambda(
value,
sumifs(
'3. Emp. Leave Setup'!J:J,
'3. Emp. Leave Setup'!B:B, value,
'3. Emp. Leave Setup'!F:F, ">=" & 'Yearly Clndr'!E2,
'3. Emp. Leave Setup'!G:G, "<=" & 'Yearly Clndr'!F2
)
)
)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论