数组公式混淆 [英] Array Formula Confusion

问题描述

我正在为数组公式而苦苦挣扎，从逻辑上讲，这听起来似乎不错，但是似乎无法正常工作.我一直在研究一个复杂的工作表，即不包括VBA，它使公式变得繁重，而改用数组.

I am struggling with an array formula, that logically seems sound, however it doesn't appear to be working correctly. I have been working on a complex sheet, that is not to include VBA, which has made it formula heavy, and using arrays instead.

在下图中，第一部分是问题，对于A-F列中显示的数据，我希望获得与I1:K1中的值匹配的值的总和.

In the image below, the first part is the problem, for the data shown in columns A-F, I wish to get a sum of the values that match the values in I1:K1.

我最初使用的公式也可以在第一张图片中看到，按F9计算得出的结果为我期望的输出20,40& amp;.50.但是，当我在公式周围添加 SUM 时，我只会得到第一个结果.

The formula I have used to begin with can be seen in the first image also, this evaluates, pressing F9, to give me the desired output 20,40 & 50. However when I add the SUM around the formula, I only get the first result out.

我认为这是一个问题，因为我没有看到树木上的树木.

I think this is an issue with me not seeing the wood for the trees on this one.

谢谢.

推荐答案

此数组公式似乎有效:

=SUM((IFERROR(MATCH(A1:F1,I1:K1,0),0)>0)*A2:F2)

这篇关于数组公式混淆的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！