If I have rows 1:m populated with values in column A, and in column B I have rows 1:n populated with values, where n < m. How can I find the sum of values in column A from row 1 up to row n.
如果有行1:m填充了列A中的值，而在列B中有行1:n填充了值，其中n < m。
In other words, how can I sum the values in Column A down to the last row of data in Column B where both Columns may have more values added to new rows at any time.
换句话说，我如何将A列中的值求和到B列中的最后一行数据，这两列随时都可能向新行添加更多的值。
2 个解决方案
#1
1
UPDATE: To ignore text values
更新:忽略文本值。
=SUMPRODUCT(IFERROR((ROW(A:A)<=MAX(IF(B:B<>"",ROW(B:B))))*A:A,0))
= SUMPRODUCT(IFERROR(行(一)< = MAX(如果(B:B < > ",行(B:B))))* A:A,0))
This will produce a 0 value when an error occurs so the formula behaves for values only.
当出现错误时，这将产生一个0值，因此公式只对值进行操作。
Original Answer:
最初的回答:
In addition to the other answer -
除了另一个答案-
If you are looking for total of A where B has a value then:
如果你要寻找A的总和B有一个值，那么:
=SUMIF(B:B,"<>",A:A)
=条件求和(“< >”,B:B:A)
This is pretty self explanitory, =SUMIF(range,criteria,[sum_range]) we check B:B for non blanks and then sum the corresponding cells from A:A.
这是非常自我解释的，=SUMIF(range,criteria，[sum_range])我们检查B:B为非空白，然后从A:A中求和相应的细胞。
If you are looking for total of A1:An where n is the last row with a value from column B then:
如果要查找A1:An，其中n是B列中最后一行的值，则:
This will need to be entered as an array formula (when in the formula bar hit Ctrl+Shift+Enter) =SUMPRODUCT((ROW(A:A)<=MAX(IF(B:B<>"",ROW(B:B))))*A:A)
这需要以数组公式的形式输入(在公式栏中按Ctrl+Shift+Enter) =SUMPRODUCT((行(A:A)<=MAX(IF(B:B<>""，行(B:B)))))*A:A)
Array formula's break down ranges and caluclate them them one cell (or row) at a time. The MAX rows is broken down as an array, the IF sormula is returning 0 for false (default) and the row number for true so MAX is then grabbing the highest row number from the array (18 in the below example).
数组公式的分解范围，并在一段时间内将它们分解为一个单元格(或行)。最大行被分解为一个数组，IF sormula返回0表示false(默认)，行号表示true，因此MAX从数组中获取最高的行号(在下面的示例中为18)。
The Sumproduct formula is then producing true/false (1 or 0) for each cell in A:A where the row is less than or equal to the MAX row of non blank entries in B:B.
然后，Sumproduct公式为A:A中的每个单元生成真/假(1或0)，其中行小于或等于B:B中的非空条目的最大行。
The formula then multiplies the true/false results (0 or 1) by the value in the corresponding A:A cell thus when the condition is met we have 1 x the value being summed and when the condition is not met then 0 will be summed (0 x the value).
这个公式然后乘以相应A的值的真/假结果(0或1):当满足条件时，我们有1个x值被求和，当条件不满足时，0将被求和(0 x值)。
I hope this helps explain the logic, feel free to probe my brain for more of an explanation if not.
我希望这有助于解释其中的逻辑，如果不是的话，请随意探索我的大脑，以获得更多的解释。
#2
0
The following formula dynamically determines the row of the last entry in column B and sums the values in column A up to this row.
下面的公式动态地确定B列最后一项的行，并将A列中的值加到这一行。
=SUM(A1:INDIRECT("A"&COUNTA(B:B)))
#1
1
UPDATE: To ignore text values
更新:忽略文本值。
=SUMPRODUCT(IFERROR((ROW(A:A)<=MAX(IF(B:B<>"",ROW(B:B))))*A:A,0))
= SUMPRODUCT(IFERROR(行(一)< = MAX(如果(B:B < > ",行(B:B))))* A:A,0))
This will produce a 0 value when an error occurs so the formula behaves for values only.
当出现错误时，这将产生一个0值，因此公式只对值进行操作。
Original Answer:
最初的回答:
In addition to the other answer -
除了另一个答案-
If you are looking for total of A where B has a value then:
如果你要寻找A的总和B有一个值，那么:
=SUMIF(B:B,"<>",A:A)
=条件求和(“< >”,B:B:A)
This is pretty self explanitory, =SUMIF(range,criteria,[sum_range]) we check B:B for non blanks and then sum the corresponding cells from A:A.
这是非常自我解释的，=SUMIF(range,criteria，[sum_range])我们检查B:B为非空白，然后从A:A中求和相应的细胞。
If you are looking for total of A1:An where n is the last row with a value from column B then:
如果要查找A1:An，其中n是B列中最后一行的值，则:
This will need to be entered as an array formula (when in the formula bar hit Ctrl+Shift+Enter) =SUMPRODUCT((ROW(A:A)<=MAX(IF(B:B<>"",ROW(B:B))))*A:A)
这需要以数组公式的形式输入(在公式栏中按Ctrl+Shift+Enter) =SUMPRODUCT((行(A:A)<=MAX(IF(B:B<>""，行(B:B)))))*A:A)
Array formula's break down ranges and caluclate them them one cell (or row) at a time. The MAX rows is broken down as an array, the IF sormula is returning 0 for false (default) and the row number for true so MAX is then grabbing the highest row number from the array (18 in the below example).
数组公式的分解范围，并在一段时间内将它们分解为一个单元格(或行)。最大行被分解为一个数组，IF sormula返回0表示false(默认)，行号表示true，因此MAX从数组中获取最高的行号(在下面的示例中为18)。
The Sumproduct formula is then producing true/false (1 or 0) for each cell in A:A where the row is less than or equal to the MAX row of non blank entries in B:B.
然后，Sumproduct公式为A:A中的每个单元生成真/假(1或0)，其中行小于或等于B:B中的非空条目的最大行。
The formula then multiplies the true/false results (0 or 1) by the value in the corresponding A:A cell thus when the condition is met we have 1 x the value being summed and when the condition is not met then 0 will be summed (0 x the value).
这个公式然后乘以相应A的值的真/假结果(0或1):当满足条件时，我们有1个x值被求和，当条件不满足时，0将被求和(0 x值)。
I hope this helps explain the logic, feel free to probe my brain for more of an explanation if not.
我希望这有助于解释其中的逻辑，如果不是的话，请随意探索我的大脑，以获得更多的解释。
#2
0
The following formula dynamically determines the row of the last entry in column B and sums the values in column A up to this row.
下面的公式动态地确定B列最后一项的行，并将A列中的值加到这一行。
=SUM(A1:INDIRECT("A"&COUNTA(B:B)))