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

[Zelena]

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...

[#2 · Accepted 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)))

[#3]

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

[#4]

@Zelena 

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.

[#5]

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!