Forum Discussion
Combining ISFORMULA and INDEX to Add Non-Formula Values Based on Column Header
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...
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)))
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
Weird. It works for me - I have tested it again.
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.
