# sumifs errors

Copper Contributor

# 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

# Re: sumifs errors

Which column contains the formulas?

Is it correct that column H features both as sum range and as one of the criteria ranges?

# Re: sumifs errors

@Hans Vogelaar

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.25 6000 0 TRUE 231-5026439-4 2 PH 9 453 \$           453.00 0 2.25 9 0 TRUE 231-5026439-4 3 CN 6000 1176 \$       1,176.00 323.4 3.25 6000 0 TRUE 231-5026439-4 4 CN 3 320 \$           320.00 80 4.25 3 0 TRUE 231-5026439-4 5 PH 249 32783 \$     32,783.00 0 5.25 249 0 TRUE 231-5026439-4 6 CN 2 124 \$           124.00 31 6.25 2 0 TRUE 231-5026439-4 7 MY 200 80962 \$     80,962.00 0 7.25 0 -200 FALSE 231-5026439-4 8 CN 2070 5664 \$       5,664.00 1416 8.25 0 -2070 FALSE 231-5026439-4 9 CN 110 117 \$           117.00 29.25 9.25 0 -110 FALSE 231-5026439-4 10 CN 55 11 \$             11.00 2.75 10.25 0 -55 FALSE 231-5026439-4 11 CN 500 213 \$           213.00 53.25 11.25 110 -390 FALSE 231-5026439-4 12 CN 4640 5287 \$       5,287.00 1321.75 12.25 55 -4585 FALSE

# Re: sumifs errors

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.

# Re: sumifs errors

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

# Re: sumifs errors

I'm afraid I cannot access your hard disk - please see my previous reply. You'll have to obtain a link in the browser.

# Re: sumifs errors

@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)

# Re: sumifs errors

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.