更改SUMIF标准的单元格引用？ [英] Changing cell reference for a SUMIF criterion?

问题描述

我试图告诉Excel根据包含每一行中的值的最后一个单元格更改SUMIF公式的单元格引用。现在，我知道我可以写一个嵌套的if语句来为我做这个，但是做一个长期的解决方案太笨拙了。例如：

$ b

在单独的报告中，此列表中的每个人都有多行记录每日销售量。在一小部分，我只需写一个用于A2，B3和C4作为标准的 SUMIF 。规模要大得多，所以我目前的解决方案是写出一个逻辑检查，并将 SUMIF 公式折叠起来。在这种情况下，E2中的公式为：

= IF （C2 = ，如果（B2 = ，SUMIF（ '范围'，A2， '范围'），SUMIF（ '范围'，B2， '范围'）），SUMIF（ '范围'，C2，范围'））

有不同的方法来告诉Excel运行 SUMIF 每行中找到最后一个值？

我希望我可以为每一行使用COUNTA，然后让SUMIF公式使用COUNTA值来更改标准单元格引用。我在这里缺少一些东西。

解决方案

= SUMIF（'range'，INDEX C2，MATCH（zzzzz，A2：C2）），'range'）

似乎值得一试。

编辑太长的评论：

我真的没有任何想法如何= MATCH（）工作，但认为它是从左到右横向搜索。当它找不到一个匹配，它只是恰好一直在看最后一个条目，（方便！）提供了。所以关键是寻找不会找到的东西。

对于人们的名字，我选择zzzzz不太可能出现。对于数字来说，理论上可以达到9.99999999999999E + 307，因此有些人使用它（例如一小时前 9 或多或少也不会有任何差异，也不可能有几个整数在 + 。

I'm trying to tell Excel to change the cell reference for a SUMIF formula based on the last cell that contains a value in each row. Right now, I know I could write a nested if statement to do this for me, but it's far too unwieldy to be a long-term solution.

Example:

On a separate report, every person in this list has multiple lines recording their daily sales. On a small scale, I'd simply write a SUMIF that uses for A2, B3 and C4 as criteria. The scale is much much larger, so my current solution is to write out a logic check with the SUMIF formula folded into it.

In this case, the formula in E2 would be:

=IF(C2="",if(B2="",SUMIF('range',A2,'range'),sumif('range',B2,'range')),sumif('range',C2,'range'))

Is there a different way to tell Excel to run the SUMIF with the last value found in each row?

I was hoping I could use COUNTA for each row, then have the SUMIF formula use the COUNTA value to change the criterion cell reference. I'm missing something here.

解决方案

=SUMIF('range',INDEX(A2:C2,MATCH("zzzzz",A2:C2)),'range')

seems worth a try.

Edit Too long for a comment:

I don’t really have any idea how =MATCH() works but think of it as traversing left to right while searching. When it fails to find a match it just happens to have been looking at the last entry and (conveniently!) offers that up. So the key is to look for something that won't be found.

For people’s names I chose "zzzzz" as unlikely to be present. For numbers anything up to 9.99999999999999E+307 is theoretically possible and hence some people use that (eg an hour ago Formula to get the first cell with a numerical value from a selection?) but I prefer a googol (1E+100) and that seems quite large enough, is much shorter – and easier to remember, even if a few 9s more or less would make no difference, nor likely what couple of integers are after the +.

这篇关于更改SUMIF标准的单元格引用？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！