sumifs errors

New Contributor

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-41MY6000TRUE
231-5026439-42PH9TRUE
231-5026439-43CN6000TRUE
231-5026439-44CN3TRUE
231-5026439-45PH249TRUE
231-5026439-46CN2TRUE
231-5026439-47MY200#SPILL!
231-5026439-48CN2070FALSE
231-5026439-49CN110FALSE
231-5026439-410CN55FALSE
231-5026439-411CN500FALSE
231-5026439-412CN4640

FALSE

 

IP Data Expeditors Lines WIP.xlsx]Sheet1

          C                                 D             G                     

231-5026439-416000
231-5026439-4 112
231-5026439-429
231-5026439-436000
231-5026439-443
231-5026439-45249
231-5026439-462
231-5026439-4710
231-5026439-47190
231-5026439-481050
231-5026439-481020
231-5026439-49110
231-5026439-41055
231-5026439-411250
231-5026439-411250
231-5026439-412890

 

 

 

 

 

7 Replies

@Mrteeser 

Which column contains the formulas?

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

@Hans Vogelaar

No column T carries the formula.  Here is the Example

    C                    E          F         H              J                     L                n

231-5026439-41MY6000660 $           660.0034.981.2500000060000TRUE
231-5026439-42PH9453 $           453.0002.2500000090TRUE
231-5026439-43CN60001176 $       1,176.00323.43.2500000060000TRUE
231-5026439-44CN3320 $           320.00804.2500000030TRUE
231-5026439-45PH24932783 $     32,783.0005.250000002490TRUE
231-5026439-46CN2124 $           124.00316.2500000020TRUE
231-5026439-47MY20080962 $     80,962.0007.250000000-200FALSE
231-5026439-48CN20705664 $       5,664.0014168.250000000-2070FALSE
231-5026439-49CN110117 $           117.0029.259.250000000-110FALSE
231-5026439-410CN5511 $             11.002.7510.250000000-55FALSE
231-5026439-411CN500213 $           213.0053.2511.25000000110-390FALSE
231-5026439-412CN46405287 $       5,287.001321.7512.2500000055-4585FALSE

@Mrteeser 

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.

@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

@Mrteeser 

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

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

@Mrteeser 

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.