Forum Discussion
Solved
SUMX calcuation error
I am trying to calculatethe child_WO total revenue and keep ending up with the amoun for the 100-5_IRE02 being added twice where only one value should be taken for the QTY 1 (or above 0) Value I am ...
Thanx for the repleis
I am not getting there - I am looking to do the following excel formula
'=SUMIFS(Revenue:Revenu;Child_WO:Child_WO;Child_WO2;QTY;">0")
Some larger data set - Revenue is already a total value and does not need to be multiplied with QTY
| WOMAIN | Child_WO | ITEMID | QTY | Revenu | Revenu_Child_WO_Required_Result |
| W0004613 | W0004613 | -594-9018 | 1 | 203.42 | =SUMIFS(E:E;B:B;B2;D:D;">0") |
| W0004613 | W0004613 | 3094540-1 | 1 | 388.8 | 13964.51 |
| W0004613 | W0004613 | 91214-2 | 0 | 0 | 13964.51 |
| W0004613 | W0004613 | 91238-2 | 0 | 0 | 13964.51 |
| W0004613 | W0004613 | 91238-2 | 0 | 0 | 13964.51 |
| W0004613 | W0004613 | 38-2_ER01 | 1 | 7982.81 | 13964.51 |
| W0004613 | W0004613 | 38-2_ER01 | 0 | 0 | 13964.51 |
| W0004613 | W0004613 | 3616755-1 | 1 | 376.24 | 13964.51 |
| W0004613 | W0004613 | -509-9002 | 1 | 4.61 | 13964.51 |
| W0004613 | W0004613 | 3810825-1 | 1 | 1755.99 | 13964.51 |
| W0004613 | W0004613 | 3827241-3 | 1 | 589.14 | 13964.51 |
| W0004613 | W0004613 | 3883857-2 | 1 | 1795.4 | 13964.51 |
| W0004613 | W0004613 | 85057-2 | 0 | 0 | 13964.51 |
| W0004613 | W0004613 | 3886189-1 | 1 | 80.83 | 13964.51 |
| W0004613 | W0004613 | 3888449-2 | 0 | 0 | 13964.51 |
| W0004613 | W0004613 | -662-9006 | 16 | 317.92 | 13964.51 |
| W0004613 | W0004613 | 895/7-300 | 1 | 17.84 | 13964.51 |
| W0004613 | W0004613 | 895-4-350 | 1 | 359.89 | 13964.51 |
| W0004613 | W0004613 | S3209-013 | 2 | 0.2 | 13964.51 |
| W0004613 | W0004613 | S3209-216 | 1 | 0.41 | 13964.51 |
| W0004613 | W0004613 | AS4824N06 | 1 | 2.71 | 13964.51 |
| W0004613 | W0004613 | 988/2-906 | 1 | 0.28 | 13964.51 |
| W0004613 | W0004613 | 988-2-011 | 12 | 1.56 | 13964.51 |
| W0004613 | W0004613 | 988-2-012 | 2 | 0.4 | 13964.51 |
| W0004613 | W0004613 | /8-G014BC | 1 | 2.63 | 13964.51 |
| W0004613 | W0004613 | 21043-3 | 14 | 4.48 | 13964.51 |
| W0004613 | W0004613 | MS21043-4 | 13 | 5.46 | 13964.51 |
| W0004613 | W0004613 | MS24622-2 | 1 | 0.13 | 13964.51 |
| W0004613 | W0004613 | S24671-14 | 1 | 0.08 | 13964.51 |
| W0004613 | W0004613 | 5083-5BB7 | 2 | 5.7 | 13964.51 |
| W0004613 | W0004613 | S35769-10 | 2 | 1.14 | 13964.51 |
| W0004613 | W0004613 | S35769-11 | 1 | 0.41 | 13964.51 |
| W0004613 | W0004613 | MS9489-06 | 1 | 0.72 | 13964.51 |
| W0004613 | W0004613 | 9556-06 | 15 | 9.75 | 13964.51 |
| W0004613 | W0004613 | 9556-06 | 15 | 9.75 | 13964.51 |
| W0004613 | W0004613 | MS9556-12 | 40 | 36 | 13964.51 |
| W0004613 | W0004613 | MS9557-12 | 9 | 9.81 | 13964.51 |
| W0004613 | W000461301 | -131-9008 | 3 | 56.16 | 17372.75 |
| W0004613 | W000461301 | -131-9010 | 6 | 56.16 | 17372.75 |
| W0004613 | W000461301 | 305766-1 | 1 | 3312 | 17372.75 |
| W0004613 | W000461301 | 352723-47 | 16 | 93.76 | 17372.75 |
| W0004613 | W000461301 | -511-9006 | 3 | 244.8 | 17372.75 |
| W0004613 | W000461301 | -511-9015 | 1 | 54.72 | 17372.75 |
| W0004613 | W000461301 | 3810684-2 | 16 | 7467.04 | 17372.75 |
| W0004613 | W000461301 | 3810820-4 | 0 | 0 | 17372.75 |
| W0004613 | W000461301 | 20-4_ER01 | 1 | 2477.04 | 17372.75 |
| W0004613 | W000461301 | 20-4_ER01 | 0 | 0 | 17372.75 |
| W0004613 | W000461301 | 3810823-3 | 1 | 3499.63 | 17372.75 |
| W0004613 | W000461301 | 10875-1 | 0 | 0 | 17372.75 |
| W0004613 | W000461301 | S3209-138 | 1 | 0.47 | 17372.75 |
| W0004613 | W000461301 | S3209-227 | 2 | 1.34 | 17372.75 |
| W0004613 | W000461301 | 21043-3 | 139 | 44.48 | 17372.75 |
| W0004613 | W000461301 | MS21043-5 | 21 | 20.58 | 17372.75 |
| W0004613 | W000461301 | S21902-6R | 1 | 11.89 | 17372.75 |
| W0004613 | W000461301 | S24665-88 | 16 | 0.32 | 17372.75 |
| W0004613 | W000461301 | MS9557-08 | 4 | 4.52 | 17372.75 |
| W0004613 | W000461301 | MS9565-05 | 18 | 13.5 | 17372.75 |
| W0004613 | W000461301 | 149C0332R | 8 | 0.32 | 17372.75 |
| W0004613 | W000461301 | 149C0363R | 50 | 3.5 | 17372.75 |
| W0004613 | W000461301 | 149C0432R | 2 | 0.18 | 17372.75 |
| W0004613 | W000461301 | 149C0563R | 21 | 1.68 | 17372.75 |
| W0004613 | W000461301 | 149E0663R | 8 | 2.32 | 17372.75 |
| W0004613 | W000461301 | 43DD3-7FC | 2 | 0.46 | 17372.75 |
| W0004613 | W000461301 | AS620C10L | 42 | 5.88 | 17372.75 |
| W0004613 | W00046130101 | 3827324-3 | 0 | 0 | 749.14 |
| W0004613 | W00046130101 | 4-3_IRE01 | 1 | 749.14 | 749.14 |
| W0004613 | W00046130101 | -603-9031 | 7 | 0 | 749.14 |
| W0004613 | W00046130102 | 3810875-1 | 0 | 0 | 803.81 |
| W0004613 | W00046130102 | 5-1_IRE03 | 1 | 803.81 | 803.81 |
| W0004613 | W00046130103 | 3810949-1 | 0 | 0 | 412.91 |
| W0004613 | W00046130103 | 9-1_IRE01 | 1 | 412.91 | 412.91 |
| W0004613 | W00046130103 | SL4-280 | 8 | 0 | 412.91 |
| W0004613 | W00046130104 | 3616866-1 | 1 | 0 | 2009.61 |
| W0004613 | W00046130104 | 3810731-1 | 1 | 0 | 2009.61 |
| W0004613 | W00046130104 | 3810948-1 | 0 | 0 | 2009.61 |
| W0004613 | W00046130104 | 8-1_IRE02 | 1 | 2009.61 | 2009.61 |
| W0004613 | W00046130105 | 0-5_IRE01 | 1 | 424.52 | 2153.89 |
| W0004613 | W00046130105 | 0-5_IRE02 | 1 | 1729.37 | 2153.89 |
| W0004613 | W00046130105 | 0-5_IRE02 | 0 | 0 | 2153.89 |
| W0004613 | W00046130105 | 21608-3 | 0 | 0 | 2153.89 |
| W0004613 | W00046130105 | 21608-3 | 0 | 0 | 2153.89 |
| W0004613 | W00046130105 | 21608-3 | 0 | 0 | 2153.89 |
| W0004613 | W00046130105 | 2662-3-02 | 5 | 0 | 2153.89 |
| W0004613 | W00046130105 | R2662-3-3 | 10 | 0 | 2153.89 |
| W0004613 | W00046130105 | MS21060L3 | 5 | 0 | 2153.89 |
| W0004613 | W00046130106 | 3827165-2 | 0 | 0 | 433.21 |
| W0004613 | W00046130106 | 5-2_IRE01 | 1 | 433.21 | 433.21 |
| W0004613 | W00046130106 | -603-9032 | 1 | 0 | 433.21 |
| W0004613 | W000461302 | 3860865-1 | 2 | 2534.36 | 8172.26 |
| W0004613 | W000461302 | 3863104-1 | 1 | 572.49 | 8172.26 |
| W0004613 | W000461302 | 3863375-1 | 4 | 193.52 | 8172.26 |
| W0004613 | W000461302 | 3884736-1 | 2 | 4771.2 | 8172.26 |
| W0004613 | W000461302 | -603-9031 | 2 | 48.76 | 8172.26 |
| W0004613 | W000461302 | -518-9003 | 1 | 25.63 | 8172.26 |
| W0004613 | W000461302 | AS3208-04 | 1 | 0.11 | 8172.26 |
| W0004613 | W000461302 | AS3208-07 | 1 | 0.12 | 8172.26 |
| W0004613 | W000461302 | AS3208-08 | 1 | 0.15 | 8172.26 |
| W0004613 | W000461302 | S3209-011 | 2 | 0.5 | 8172.26 |
| W0004613 | W000461302 | S3209-022 | 1 | 0.2 | 8172.26 |
| W0004613 | W000461302 | S3209-108 | 1 | 0.25 | 8172.26 |
| W0004613 | W000461302 | S3209-112 | 2 | 0.3 | 8172.26 |
| W0004613 | W000461302 | S3209-123 | 1 | 0.23 | 8172.26 |
| W0004613 | W000461302 | S3209-125 | 1 | 0.31 | 8172.26 |
| W0004613 | W000461302 | S3209-143 | 3 | 1.68 | 8172.26 |
| W0004613 | W000461302 | 248-1-906 | 1 | 0.12 | 8172.26 |
| W0004613 | W000461302 | 21043-3 | 2 | 0.64 | 8172.26 |
| W0004613 | W000461302 | MS9489-05 | 7 | 3.99 | 8172.26 |
| W0004613 | W000461302 | MS9489-07 | 3 | 1.65 | 8172.26 |
| W0004613 | W000461302 | MS9489-18 | 4 | 3.04 | 8172.26 |
| W0004613 | W000461302 | MS9556-08 | 4 | 3.08 | 8172.26 |
| W0004613 | W000461302 | MS9556-14 | 8 | 9.04 | 8172.26 |
| W0004613 | W000461302 | MS9556-24 | 1 | 0.89 | 8172.26 |
| W0004613 | W000461303 | 310829-5 | 1 | 58.13414349 | 5933.64978 |
| W0004613 | W000461303 | 3179505-1 | 1 | 193.8456365 | 5933.64978 |
| W0004613 | W000461303 | 3291214-2 | 0 | 0 | 5933.64978 |
| W0004613 | W000461303 | 4-49-7044 | 1 | 0 | 5933.64978 |
| W0004613 | W000461303 | 4-49-7045 | 1 | 0 | 5933.64978 |
| W0004613 | W000461303 | E+49-7045 | 1 | 5658.05 | 5933.64978 |
| W0004613 | W000461303 | -522-9103 | 1 | 13.92 | 5933.64978 |
| W0004613 | W000461303 | -504-9300 | 3 | 1.08 | 5933.64978 |
| W0004613 | W000461303 | -509-9004 | 2 | 1.96 | 5933.64978 |
| W0004613 | W000461303 | 8-5-BEB-0 | 17 | 1.7 | 5933.64978 |
| W0004613 | W000461303 | 029/4-110 | 2 | 0.58 | 5933.64978 |
| W0004613 | W000461303 | MS21043-3 | 8 | 2.56 | 5933.64978 |
| W0004613 | W000461303 | 35275-207 | 1 | 0.32 | 5933.64978 |
| W0004613 | W000461303 | 35275-214 | 1 | 0.22 | 5933.64978 |
| W0004613 | W000461303 | 35275-217 | 4 | 1.28 | 5933.64978 |
| W0004613 | W000461304 | 49-Repair | 1 | 4237.86 | 4237.86 |
| W0004613 | W000461304 | -118-1300 | 2 | 0 | 4237.86 |
| W0004613 | W000461304 | -503-9003 | 4 | 0 | 4237.86 |
| W0004613 | W000461304 | /13-003-0 | 60 | 0 | 4237.86 |
| W0004613 | W000461304 | 3/5-105-9 | 10 | 0 | 4237.86 |
| W0004613 | W000461304 | 49-52S14N | 1 | 0 | 4237.86 |
| W0004613 | W000461304 | 3368-1-8A | 8 | 0 | 4237.86 |
| W0004613 | W000461304 | 3368-5-8E | 16 | 0 | 4237.86 |
| W0004613 | W000461305 | 3885057-2 | 0 | 0 | 200.95 |
| W0004613 | W000461305 | 7-2_IRE01 | 1 | 200.95 | 200.95 |