SOLVED

Combining ISFORMULA and INDEX to Add Non-Formula Values Based on Column Header

%3CLINGO-SUB%20id%3D%22lingo-sub-2283124%22%20slang%3D%22en-US%22%3ECombining%20ISFORMULA%20and%20INDEX%20to%20Add%20Non-Formula%20Values%20Based%20on%20Column%20Header%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2283124%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20attempting%20to%20combine%20ISFORMULA%20and%20INDEX%20in%20a%20SUM%20formula%20and%20for%20some%20reason%20am%20hitting%20a%20wall.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUM(INDEX('Invoices%20to%20be%20Paid'!A1%3AZZ7000%2C%2CMATCH(%22Amount%22%2C'Invoices%20to%20be%20Paid'!1%3A1%2C0)))%3C%2FP%3E%3CP%3E%5EThis%20formula%20works%2C%20but%20the%20problem%20is%20that%20it's%20summing%26nbsp%3B%3CU%3Eall%3C%2FU%3E%20cells%2C%20not%20just%20those%20that%20don't%20have%20a%20formula%20in%20them%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUM(IFERROR((ISFORMULA('Invoices%20to%20be%20Paid'!%24F%241%3A%24F%247000)%3DFALSE)*'Invoices%20to%20be%20Paid'!%24F%241%3A%24F%247000%2C0))%3C%2FP%3E%3CP%3E%5EThis%20formula%20also%20works%2C%20but%20it%20requires%20the%20data%20to%20always%20be%20in%20column%20'F'%20and%20there's%20a%20possibility%20it%20could%20move%20around%2C%20so%20I%20want%20to%20use%20the%20INDEX-MATCH%20to%20ensure%20I'm%20pulling%20from%20the%20column%20with%20the%20%22Amount%22%20header%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAll%20my%20attempts%20to%20somehow%20merge%20these%20two%20formulas%20have%20been%20wildly%20unsuccessful%20and%20I%20feel%20like%20I'm%20probably%20missing%20something%20obvious%2C%20but%20I've%20wasted%20far%20too%20much%20time%20on%20this%20and%20would%20love%20any%20ideas...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2283124%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-2283164%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20ISFORMULA%20and%20INDEX%20to%20Add%20Non-Formula%20Values%20Based%20on%20Column%20Header%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2283164%22%20slang%3D%22en-US%22%3EThat's%20bizarre...%20I%20moved%20it%20into%20a%20new%20workbook%20per%20your%20request%20and%20it%20worked%2C%20so%20I%20retried%20it%20in%20the%20original%20and%20then%20it%20worked%20there%20as%20well%20(literally%20no%20changes-%20copy%20%26amp%3B%20pasted%20a%20second%20time).%20No%20idea%20what%20went%20wrong%20the%20first%20time%20through%2C%20but%20thanks%20so%20much%20for%20the%20assistance!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2283155%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20ISFORMULA%20and%20INDEX%20to%20Add%20Non-Formula%20Values%20Based%20on%20Column%20Header%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2283155%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1035304%22%20target%3D%22_blank%22%3E%40Zelena%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWeird.%20It%20works%20for%20me%20-%20I%20have%20tested%20it%20again.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0341.png%22%20style%3D%22width%3A%20501px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F275187i633C094A55921D86%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22S0341.png%22%20alt%3D%22S0341.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0342.png%22%20style%3D%22width%3A%20204px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F275189iB2BDD2FE0AE4CB6B%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22S0342.png%22%20alt%3D%22S0342.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ECould%20you%20create%20a%20stripped-down%20copy%20of%20the%20workbook%20(without%20sensitive%20information)%20and%20make%20it%20available%20through%20one%20of%20the%20websites%20that%20let%20you%20upload%20and%20share%20a%20file%2C%20such%20as%20OneDrive%2C%20Google%20Drive%2C%20FileDropper%20or%20DropBox.%20Then%20post%20a%20link%20to%20the%20uploaded%20and%20shared%20file%20here.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2283142%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20ISFORMULA%20and%20INDEX%20to%20Add%20Non-Formula%20Values%20Based%20on%20Column%20Header%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2283142%22%20slang%3D%22en-US%22%3EIt%20spits%20out%20%22TRUE%22%20but%20no%20number%20value%2C%20I'm%20afraid%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2283137%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20ISFORMULA%20and%20INDEX%20to%20Add%20Non-Formula%20Values%20Based%20on%20Column%20Header%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2283137%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1035304%22%20target%3D%22_blank%22%3E%40Zelena%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDoes%20this%20do%20what%20you%20want%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMPRODUCT('Invoices%20to%20be%20Paid'!A2%3AZZ7000%2C('Invoices%20to%20be%20Paid'!A1%3AZZ1%3D%22Amount%22)*NOT(ISFORMULA('Invoices%20to%20be%20Paid'!A2%3AZZ7000)))%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I'm attempting to combine ISFORMULA and INDEX in a SUM formula and for some reason am hitting a wall.

 

=SUM(INDEX('Invoices to be Paid'!A1:ZZ7000,,MATCH("Amount",'Invoices to be Paid'!1:1,0)))

^This formula works, but the problem is that it's summing all cells, not just those that don't have a formula in them

 

=SUM(IFERROR((ISFORMULA('Invoices to be Paid'!$F$1:$F$7000)=FALSE)*'Invoices to be Paid'!$F$1:$F$7000,0))

^This formula also works, but it requires the data to always be in column 'F' and there's a possibility it could move around, so I want to use the INDEX-MATCH to ensure I'm pulling from the column with the "Amount" header

 

All my attempts to somehow merge these two formulas have been wildly unsuccessful and I feel like I'm probably missing something obvious, but I've wasted far too much time on this and would love any ideas...

4 Replies
best response confirmed by Zelena (New Contributor)
Solution

@Zelena 

Does this do what you want?

 

=SUMPRODUCT('Invoices to be Paid'!A2:ZZ7000,('Invoices to be Paid'!A1:ZZ1="Amount")*NOT(ISFORMULA('Invoices to be Paid'!A2:ZZ7000)))

It spits out "TRUE" but no number value, I'm afraid

@Zelena 

Weird. It works for me - I have tested it again.

 

S0341.png

 

S0342.png

Could you create a stripped-down copy of the workbook (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.

That's bizarre... I moved it into a new workbook per your request and it worked, so I retried it in the original and then it worked there as well (literally no changes- copy & pasted a second time). No idea what went wrong the first time through, but thanks so much for the assistance!