May 10 2022 07:48 AM
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 |
May 10 2022 11:50 AM
Which column contains the formulas?
Is it correct that column H features both as sum range and as one of the criteria ranges?
May 10 2022 12:41 PM
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 |
May 10 2022 12:46 PM
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.
May 10 2022 01:33 PM
@Hans Vogelaar C:\Users\055885\OneDrive - Avnet\Excel
The task is to match TxnQty from sheet A with Qty sheet B which makes Delta a 0 and turns V to a True
Did this work/help
May 10 2022 01:45 PM
I'm afraid I cannot access your hard disk - please see my previous reply. You'll have to obtain a link in the browser.
May 16 2022 05:39 AM
@Hans Vogelaar Here is the code:
=SUMIFS('[IP Data Expeditors Lines WIP.xlsx]Sheet1'!$G:$G,'[IP Data Expeditors Lines WIP.xlsx]Sheet1'!$C:$C,C111904,'[IP Data Expeditors Lines WIP.xlsx]Sheet1'!$D:$D,E111904)
May 16 2022 06:19 AM
Once again...
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.