同一个单元格中 3 个数字的总和也使用参考公式 [英] Sum of 3 numbers in same cell using a reference formula as well
问题描述
在 B1 我有:3,4,5在 B2 我有这些数字的总和 12
IN B1 I have: 3,4,5 IN B2 I have the sum of these numbers 12
我想把这些加起来,然后出现在 B3 中
I would like to add these together and the sum to appear in B3
因此,对于 B3,我需要告诉 excel 引用包含数字的单元格,然后将它们相加.这样,一旦我创建了原始公式,我就可以将其拖过剩余的单元格.
So, for B3, I need to tell excel to reference the cell holding the numbers, then add them together. This way, once I create the original formula, I can drag it through the remaining cells.
原因是:这些数字的总和 (B2) 是从电子表格中的不同位置提取总和.我需要确保这些数字匹配.
Reason is: The sum of these numbers (B2), are pulling the sum from a different location within the spreadsheet. I need to make sure these numbers match.
谢谢
推荐答案
替代解决方案(如果正好是由逗号分隔的三个数字):
Alternate solution (if exactly three numbers separated by a comma):
=SUMPRODUCT(--MID(SUBSTITUTE(B1,",",REPT(" ",99)),99*(ROW($1:$3)-1)+1,99))
或者,使公式更加动态,以便 B1 单元格可以包含任意数量的数字,只要它们以逗号分隔:
Or, to make the formula more dynamic so that the B1 cell can have any quantity of numbers, as long as they are separated by a comma:
=SUMPRODUCT(--MID(SUBSTITUTE(B1,",",REPT(" ",99)),99*(ROW(INDIRECT("1:"&LEN(B1)-LEN(SUBSTITUTE(B1,",",""))+1))-1)+1,99))
这篇关于同一个单元格中 3 个数字的总和也使用参考公式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!