I have the following table:
我有下表:
I want to compare the values of Column A elements against its mapped equivalent in Column D. See the mapping table in Range I:J
我想比较列A元素的值与列D中映射的等效值。请参阅范围I中的映射表:J
The first one is easy, in Column D, 211010 has a value of 9,570 so does its mapped equivalent (B1001)in column A, so difference is zero.
第一个很容易,在D列中,211010的值为9,570,因此它在A列中的映射等效值(B1001),因此差值为零。
However, the next element 222010 is mapped to two elements B30 and B3030
然而,下一个元素222010被映射到两个元素B30和B3030
What I want is a formula in column Zthat adds up values of elements in column D; for example 222010 which is -3,041 and compares it to the sum of its mapped equivalent in column A (B30 & B3030) which is (-6327+3286) also 3,041 and returns the difference which may be zero or otherwise.
我想要的是Z列中的公式,它将D列中的元素值相加;例如222010,其为-3,041,并将其与列A中的映射等效值(B30和B3030)的总和(其为(-6327 + 3286)也为3,041)进行比较,并返回可能为零或其他的差值。
I tried using combining SUMIF/+IndexMatch/VLOOKUP but I couldn't get it to work for me.
我尝试使用SUMIF / + IndexMatch / VLOOKUP,但我无法让它为我工作。
Any help will be appreciated.
任何帮助将不胜感激。
1 个解决方案
#1
2
You can get the first part with a simple SUMIF, i.e.
您可以使用简单的SUMIF获得第一部分,即
=SUMIF(D$2:D$17,D2,E$2:E$17)
and then the latter with this "array formula"
然后后者用这个“数组公式”
=SUM(IF(ISNUMBER(MATCH(A$2:A$17,IF(I$2:I$17=D2,H$2:H$17),0)),B$2:B$17))
confirmed with CTRL+SHIFT+ENTER
用CTRL + SHIFT + ENTER确认
You can simply subtract one from the other for your comparison
您可以简单地从另一个中减去一个用于比较
#1
2
You can get the first part with a simple SUMIF, i.e.
您可以使用简单的SUMIF获得第一部分,即
=SUMIF(D$2:D$17,D2,E$2:E$17)
and then the latter with this "array formula"
然后后者用这个“数组公式”
=SUM(IF(ISNUMBER(MATCH(A$2:A$17,IF(I$2:I$17=D2,H$2:H$17),0)),B$2:B$17))
confirmed with CTRL+SHIFT+ENTER
用CTRL + SHIFT + ENTER确认
You can simply subtract one from the other for your comparison
您可以简单地从另一个中减去一个用于比较