Forum Discussion
sumifs errors
I am using the following formula to match data from two different spreadsheets. This works for the individual row but I need to do around a thousand rows. When I hit the + to copy down to the remaining rows the formula does not work. Also some rows have multiple cells to match the corresponding sheet.
Any ideas would help, thank you.
=SUMIFS(H:H,C:C,'[IP Data Expeditors Lines WIP.xlsx]Sheet1'!$C233785,H:H,'[IP Data Expeditors Lines WIP.xlsx]Sheet1'!$G233785,E:E,'[IP Data Expeditors Lines WIP.xlsx]Sheet1'!$D233785)
Sheet 1 book A
C E F H V
| 231-5026439-4 | 1 | MY | 6000 | TRUE |
| 231-5026439-4 | 2 | PH | 9 | TRUE |
| 231-5026439-4 | 3 | CN | 6000 | TRUE |
| 231-5026439-4 | 4 | CN | 3 | TRUE |
| 231-5026439-4 | 5 | PH | 249 | TRUE |
| 231-5026439-4 | 6 | CN | 2 | TRUE |
| 231-5026439-4 | 7 | MY | 200 | #SPILL! |
| 231-5026439-4 | 8 | CN | 2070 | FALSE |
| 231-5026439-4 | 9 | CN | 110 | FALSE |
| 231-5026439-4 | 10 | CN | 55 | FALSE |
| 231-5026439-4 | 11 | CN | 500 | FALSE |
| 231-5026439-4 | 12 | CN | 4640 | FALSE |
IP Data Expeditors Lines WIP.xlsx]Sheet1
C D G
| 231-5026439-4 | 1 | 6000 |
| 231-5026439-4 | 112 | |
| 231-5026439-4 | 2 | 9 |
| 231-5026439-4 | 3 | 6000 |
| 231-5026439-4 | 4 | 3 |
| 231-5026439-4 | 5 | 249 |
| 231-5026439-4 | 6 | 2 |
| 231-5026439-4 | 7 | 10 |
| 231-5026439-4 | 7 | 190 |
| 231-5026439-4 | 8 | 1050 |
| 231-5026439-4 | 8 | 1020 |
| 231-5026439-4 | 9 | 110 |
| 231-5026439-4 | 10 | 55 |
| 231-5026439-4 | 11 | 250 |
| 231-5026439-4 | 11 | 250 |
| 231-5026439-4 | 12 | 890 |
7 Replies
Which column contains the formulas?
Is it correct that column H features both as sum range and as one of the criteria ranges?
No column T carries the formula. Here is the Example
C E F H J L n
231-5026439-4 1 MY 6000 660 $ 660.00 34.98 1.25000000 6000 0 TRUE 231-5026439-4 2 PH 9 453 $ 453.00 0 2.25000000 9 0 TRUE 231-5026439-4 3 CN 6000 1176 $ 1,176.00 323.4 3.25000000 6000 0 TRUE 231-5026439-4 4 CN 3 320 $ 320.00 80 4.25000000 3 0 TRUE 231-5026439-4 5 PH 249 32783 $ 32,783.00 0 5.25000000 249 0 TRUE 231-5026439-4 6 CN 2 124 $ 124.00 31 6.25000000 2 0 TRUE 231-5026439-4 7 MY 200 80962 $ 80,962.00 0 7.25000000 0 -200 FALSE 231-5026439-4 8 CN 2070 5664 $ 5,664.00 1416 8.25000000 0 -2070 FALSE 231-5026439-4 9 CN 110 117 $ 117.00 29.25 9.25000000 0 -110 FALSE 231-5026439-4 10 CN 55 11 $ 11.00 2.75 10.25000000 0 -55 FALSE 231-5026439-4 11 CN 500 213 $ 213.00 53.25 11.25000000 110 -390 FALSE 231-5026439-4 12 CN 4640 5287 $ 5,287.00 1321.75 12.25000000 55 -4585 FALSE I'm afraid that doesn't help. Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.
