sumifs errors

%3CLINGO-SUB%20id%3D%22lingo-sub-3351530%22%20slang%3D%22en-US%22%3Esumifs%20errors%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3351530%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20using%20the%20following%20formula%20to%20match%20data%20from%20two%20different%20spreadsheets.%26nbsp%3B%20This%20works%20for%20the%20individual%20row%20but%20I%20need%20to%20do%20around%20a%20thousand%20rows.%26nbsp%3B%20When%20I%20hit%20the%20%2B%20to%20copy%20down%20to%20the%20remaining%20rows%20the%20formula%20does%20not%20work.%26nbsp%3B%20Also%20some%20rows%20have%20multiple%20cells%20to%20match%20the%20corresponding%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%20would%20help%2C%20thank%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMIFS(H%3AH%2CC%3AC%2C'%5BIP%20Data%20Expeditors%20Lines%20WIP.xlsx%5DSheet1'!%24C233785%2CH%3AH%2C'%5BIP%20Data%20Expeditors%20Lines%20WIP.xlsx%5DSheet1'!%24G233785%2CE%3AE%2C'%5BIP%20Data%20Expeditors%20Lines%20WIP.xlsx%5DSheet1'!%24D233785)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESheet%201%20book%20A%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BC%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20F%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BH%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20V%3C%2FP%3E%3CTABLE%20width%3D%22331px%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2295.7188px%22%3E231-5026439-4%3C%2FTD%3E%3CTD%20width%3D%2240.9844px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3EMY%3C%2FTD%3E%3CTD%20width%3D%2275.5312px%22%3E6000%3C%2FTD%3E%3CTD%20width%3D%2277.7656px%22%3ETRUE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2295.7188px%22%3E231-5026439-4%3C%2FTD%3E%3CTD%20width%3D%2240.9844px%22%3E2%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3EPH%3C%2FTD%3E%3CTD%20width%3D%2275.5312px%22%3E9%3C%2FTD%3E%3CTD%20width%3D%2277.7656px%22%3ETRUE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2295.7188px%22%3E231-5026439-4%3C%2FTD%3E%3CTD%20width%3D%2240.9844px%22%3E3%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3ECN%3C%2FTD%3E%3CTD%20width%3D%2275.5312px%22%3E6000%3C%2FTD%3E%3CTD%20width%3D%2277.7656px%22%3ETRUE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2295.7188px%22%3E231-5026439-4%3C%2FTD%3E%3CTD%20width%3D%2240.9844px%22%3E4%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3ECN%3C%2FTD%3E%3CTD%20width%3D%2275.5312px%22%3E3%3C%2FTD%3E%3CTD%20width%3D%2277.7656px%22%3ETRUE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2295.7188px%22%3E231-5026439-4%3C%2FTD%3E%3CTD%20width%3D%2240.9844px%22%3E5%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3EPH%3C%2FTD%3E%3CTD%20width%3D%2275.5312px%22%3E249%3C%2FTD%3E%3CTD%20width%3D%2277.7656px%22%3ETRUE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2295.7188px%22%3E231-5026439-4%3C%2FTD%3E%3CTD%20width%3D%2240.9844px%22%3E6%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3ECN%3C%2FTD%3E%3CTD%20width%3D%2275.5312px%22%3E2%3C%2FTD%3E%3CTD%20width%3D%2277.7656px%22%3ETRUE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2295.7188px%22%3E231-5026439-4%3C%2FTD%3E%3CTD%20width%3D%2240.9844px%22%3E7%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3EMY%3C%2FTD%3E%3CTD%20width%3D%2275.5312px%22%3E200%3C%2FTD%3E%3CTD%20width%3D%2277.7656px%22%3E%23SPILL!%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2295.7188px%22%3E231-5026439-4%3C%2FTD%3E%3CTD%20width%3D%2240.9844px%22%3E8%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3ECN%3C%2FTD%3E%3CTD%20width%3D%2275.5312px%22%3E2070%3C%2FTD%3E%3CTD%20width%3D%2277.7656px%22%3EFALSE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2295.7188px%22%3E231-5026439-4%3C%2FTD%3E%3CTD%20width%3D%2240.9844px%22%3E9%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3ECN%3C%2FTD%3E%3CTD%20width%3D%2275.5312px%22%3E110%3C%2FTD%3E%3CTD%20width%3D%2277.7656px%22%3EFALSE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2295.7188px%22%3E231-5026439-4%3C%2FTD%3E%3CTD%20width%3D%2240.9844px%22%3E10%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3ECN%3C%2FTD%3E%3CTD%20width%3D%2275.5312px%22%3E55%3C%2FTD%3E%3CTD%20width%3D%2277.7656px%22%3EFALSE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2295.7188px%22%3E231-5026439-4%3C%2FTD%3E%3CTD%20width%3D%2240.9844px%22%3E11%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3ECN%3C%2FTD%3E%3CTD%20width%3D%2275.5312px%22%3E500%3C%2FTD%3E%3CTD%20width%3D%2277.7656px%22%3EFALSE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2295.7188px%22%3E231-5026439-4%3C%2FTD%3E%3CTD%20width%3D%2240.9844px%22%3E12%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3ECN%3C%2FTD%3E%3CTD%20width%3D%2275.5312px%22%3E4640%3C%2FTD%3E%3CTD%20width%3D%2277.7656px%22%3E%3CP%3EFALSE%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIP%20Data%20Expeditors%20Lines%20WIP.xlsx%5DSheet1%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20C%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BD%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BG%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22347px%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22167.5px%22%3E231-5026439-4%3C%2FTD%3E%3CTD%20width%3D%2270.8281px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%22107.672px%22%3E6000%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22167.5px%22%3E231-5026439-4%3C%2FTD%3E%3CTD%20width%3D%2270.8281px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22107.672px%22%3E112%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22167.5px%22%3E231-5026439-4%3C%2FTD%3E%3CTD%20width%3D%2270.8281px%22%3E2%3C%2FTD%3E%3CTD%20width%3D%22107.672px%22%3E9%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22167.5px%22%3E231-5026439-4%3C%2FTD%3E%3CTD%20width%3D%2270.8281px%22%3E3%3C%2FTD%3E%3CTD%20width%3D%22107.672px%22%3E6000%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22167.5px%22%3E231-5026439-4%3C%2FTD%3E%3CTD%20width%3D%2270.8281px%22%3E4%3C%2FTD%3E%3CTD%20width%3D%22107.672px%22%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22167.5px%22%3E231-5026439-4%3C%2FTD%3E%3CTD%20width%3D%2270.8281px%22%3E5%3C%2FTD%3E%3CTD%20width%3D%22107.672px%22%3E249%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22167.5px%22%3E231-5026439-4%3C%2FTD%3E%3CTD%20width%3D%2270.8281px%22%3E6%3C%2FTD%3E%3CTD%20width%3D%22107.672px%22%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22167.5px%22%3E231-5026439-4%3C%2FTD%3E%3CTD%20width%3D%2270.8281px%22%3E7%3C%2FTD%3E%3CTD%20width%3D%22107.672px%22%3E10%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22167.5px%22%3E231-5026439-4%3C%2FTD%3E%3CTD%20width%3D%2270.8281px%22%3E7%3C%2FTD%3E%3CTD%20width%3D%22107.672px%22%3E190%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22167.5px%22%3E231-5026439-4%3C%2FTD%3E%3CTD%20width%3D%2270.8281px%22%3E8%3C%2FTD%3E%3CTD%20width%3D%22107.672px%22%3E1050%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22167.5px%22%3E231-5026439-4%3C%2FTD%3E%3CTD%20width%3D%2270.8281px%22%3E8%3C%2FTD%3E%3CTD%20width%3D%22107.672px%22%3E1020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22167.5px%22%3E231-5026439-4%3C%2FTD%3E%3CTD%20width%3D%2270.8281px%22%3E9%3C%2FTD%3E%3CTD%20width%3D%22107.672px%22%3E110%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22167.5px%22%3E231-5026439-4%3C%2FTD%3E%3CTD%20width%3D%2270.8281px%22%3E10%3C%2FTD%3E%3CTD%20width%3D%22107.672px%22%3E55%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22167.5px%22%3E231-5026439-4%3C%2FTD%3E%3CTD%20width%3D%2270.8281px%22%3E11%3C%2FTD%3E%3CTD%20width%3D%22107.672px%22%3E250%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22167.5px%22%3E231-5026439-4%3C%2FTD%3E%3CTD%20width%3D%2270.8281px%22%3E11%3C%2FTD%3E%3CTD%20width%3D%22107.672px%22%3E250%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22167.5px%22%3E231-5026439-4%3C%2FTD%3E%3CTD%20width%3D%2270.8281px%22%3E12%3C%2FTD%3E%3CTD%20width%3D%22107.672px%22%3E890%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3351530%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3353091%22%20slang%3D%22en-US%22%3ERe%3A%20sumifs%20errors%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3353091%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1386007%22%20target%3D%22_blank%22%3E%40Mrteeser%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhich%20column%20contains%20the%20formulas%3F%3C%2FP%3E%0A%3CP%3EIs%20it%20correct%20that%20column%20H%20features%20both%20as%20sum%20range%20and%20as%20one%20of%20the%20criteria%20ranges%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3353315%22%20slang%3D%22en-US%22%3ERe%3A%20sumifs%20errors%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3353315%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%3C%2FP%3E%3CP%3ENo%20column%20T%20carries%20the%20formula.%26nbsp%3B%20Here%20is%20the%20Example%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20C%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20F%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BH%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20J%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BL%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20n%3C%2FP%3E%3CTABLE%20width%3D%22821%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22104%22%3E231-5026439-4%3C%2FTD%3E%3CTD%20width%3D%2246%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2239%22%3EMY%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E6000%3C%2FTD%3E%3CTD%20width%3D%2281%22%3E660%3C%2FTD%3E%3CTD%20width%3D%2291%22%3E%26nbsp%3B%24%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20660.00%3C%2FTD%3E%3CTD%20width%3D%2254%22%3E34.98%3C%2FTD%3E%3CTD%20width%3D%22104%22%3E1.25000000%3C%2FTD%3E%3CTD%20width%3D%2263%22%3E6000%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E0%3C%2FTD%3E%3CTD%20width%3D%2285%22%3ETRUE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E231-5026439-4%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3EPH%3C%2FTD%3E%3CTD%3E9%3C%2FTD%3E%3CTD%3E453%3C%2FTD%3E%3CTD%3E%26nbsp%3B%24%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20453.00%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E2.25000000%3C%2FTD%3E%3CTD%3E9%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3ETRUE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E231-5026439-4%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3ECN%3C%2FTD%3E%3CTD%3E6000%3C%2FTD%3E%3CTD%3E1176%3C%2FTD%3E%3CTD%3E%26nbsp%3B%24%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%2C176.00%3C%2FTD%3E%3CTD%3E323.4%3C%2FTD%3E%3CTD%3E3.25000000%3C%2FTD%3E%3CTD%3E6000%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3ETRUE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E231-5026439-4%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3ECN%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E320%3C%2FTD%3E%3CTD%3E%26nbsp%3B%24%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20320.00%3C%2FTD%3E%3CTD%3E80%3C%2FTD%3E%3CTD%3E4.25000000%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3ETRUE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E231-5026439-4%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3EPH%3C%2FTD%3E%3CTD%3E249%3C%2FTD%3E%3CTD%3E32783%3C%2FTD%3E%3CTD%3E%26nbsp%3B%24%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2032%2C783.00%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E5.25000000%3C%2FTD%3E%3CTD%3E249%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3ETRUE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E231-5026439-4%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3ECN%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E124%3C%2FTD%3E%3CTD%3E%26nbsp%3B%24%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20124.00%3C%2FTD%3E%3CTD%3E31%3C%2FTD%3E%3CTD%3E6.25000000%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3ETRUE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E231-5026439-4%3C%2FTD%3E%3CTD%3E7%3C%2FTD%3E%3CTD%3EMY%3C%2FTD%3E%3CTD%3E200%3C%2FTD%3E%3CTD%3E80962%3C%2FTD%3E%3CTD%3E%26nbsp%3B%24%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2080%2C962.00%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E7.25000000%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E-200%3C%2FTD%3E%3CTD%3EFALSE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E231-5026439-4%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3CTD%3ECN%3C%2FTD%3E%3CTD%3E2070%3C%2FTD%3E%3CTD%3E5664%3C%2FTD%3E%3CTD%3E%26nbsp%3B%24%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%205%2C664.00%3C%2FTD%3E%3CTD%3E1416%3C%2FTD%3E%3CTD%3E8.25000000%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E-2070%3C%2FTD%3E%3CTD%3EFALSE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E231-5026439-4%3C%2FTD%3E%3CTD%3E9%3C%2FTD%3E%3CTD%3ECN%3C%2FTD%3E%3CTD%3E110%3C%2FTD%3E%3CTD%3E117%3C%2FTD%3E%3CTD%3E%26nbsp%3B%24%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20117.00%3C%2FTD%3E%3CTD%3E29.25%3C%2FTD%3E%3CTD%3E9.25000000%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E-110%3C%2FTD%3E%3CTD%3EFALSE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E231-5026439-4%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3ECN%3C%2FTD%3E%3CTD%3E55%3C%2FTD%3E%3CTD%3E11%3C%2FTD%3E%3CTD%3E%26nbsp%3B%24%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2011.00%3C%2FTD%3E%3CTD%3E2.75%3C%2FTD%3E%3CTD%3E10.25000000%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E-55%3C%2FTD%3E%3CTD%3EFALSE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E231-5026439-4%3C%2FTD%3E%3CTD%3E11%3C%2FTD%3E%3CTD%3ECN%3C%2FTD%3E%3CTD%3E500%3C%2FTD%3E%3CTD%3E213%3C%2FTD%3E%3CTD%3E%26nbsp%3B%24%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20213.00%3C%2FTD%3E%3CTD%3E53.25%3C%2FTD%3E%3CTD%3E11.25000000%3C%2FTD%3E%3CTD%3E110%3C%2FTD%3E%3CTD%3E-390%3C%2FTD%3E%3CTD%3EFALSE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E231-5026439-4%3C%2FTD%3E%3CTD%3E12%3C%2FTD%3E%3CTD%3ECN%3C%2FTD%3E%3CTD%3E4640%3C%2FTD%3E%3CTD%3E5287%3C%2FTD%3E%3CTD%3E%26nbsp%3B%24%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%205%2C287.00%3C%2FTD%3E%3CTD%3E1321.75%3C%2FTD%3E%3CTD%3E12.25000000%3C%2FTD%3E%3CTD%3E55%3C%2FTD%3E%3CTD%3E-4585%3C%2FTD%3E%3CTD%3EFALSE%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3353346%22%20slang%3D%22en-US%22%3ERe%3A%20sumifs%20errors%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3353346%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1386007%22%20target%3D%22_blank%22%3E%40Mrteeser%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'm%20afraid%20that%20doesn't%20help.%20Could%20you%20attach%20a%20sample%20workbook%20(without%20sensitive%20data)%2C%20or%20if%20that%20is%20not%20possible%2C%20make%20it%20available%20through%20OneDrive%2C%20Google%20Drive%2C%20Dropbox%20or%20similar%3F%20Thanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3353704%22%20slang%3D%22en-US%22%3ERe%3A%20sumifs%20errors%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3353704%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3CA%20title%3D%22Sumifs%20workbook%22%20href%3D%22C%3A%5CUsers%5C055885%5COneDrive%20-%20Avnet%5CExcel%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EC%3A%5CUsers%5C055885%5COneDrive%20-%20Avnet%5CExcel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20task%20is%20to%20match%20TxnQty%20from%20sheet%20A%20with%20Qty%20sheet%20B%20which%20makes%20Delta%20a%200%20and%20turns%20V%20to%20a%20True%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDid%20this%20work%2Fhelp%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3353817%22%20slang%3D%22en-US%22%3ERe%3A%20sumifs%20errors%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3353817%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1386007%22%20target%3D%22_blank%22%3E%40Mrteeser%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'm%20afraid%20I%20cannot%20access%20your%20hard%20disk%20-%20please%20see%20my%20previous%20reply.%20You'll%20have%20to%20obtain%20a%20link%20in%20the%20browser.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3380066%22%20slang%3D%22en-US%22%3ERe%3A%20sumifs%20errors%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3380066%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3BHere%20is%20the%20code%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMIFS('%5BIP%20Data%20Expeditors%20Lines%20WIP.xlsx%5DSheet1'!%24G%3A%24G%2C'%5BIP%20Data%20Expeditors%20Lines%20WIP.xlsx%5DSheet1'!%24C%3A%24C%2CC111904%2C'%5BIP%20Data%20Expeditors%20Lines%20WIP.xlsx%5DSheet1'!%24D%3A%24D%2CE111904)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3380303%22%20slang%3D%22en-US%22%3ERe%3A%20sumifs%20errors%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3380303%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1386007%22%20target%3D%22_blank%22%3E%40Mrteeser%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOnce%20again...%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20attach%20a%20sample%20workbook%20(without%20sensitive%20data)%2C%20or%20if%20that%20is%20not%20possible%2C%20make%20it%20available%20through%20OneDrive%2C%20Google%20Drive%2C%20Dropbox%20or%20similar%3F%20Thanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional 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.