Add Invoice Amounts and Paid Amounts based on user inputs.

Unmerge the Total Invoiced cell E7 because array formulas won't work with merged cells. Then use the below formula and press Ctrl+Shift+Enter to confirm it as an array.

=SUMPRODUCT(IF(B3<>0,Table2[Paid Date]=InvoicingForm!B3,1)*IF(B5<>0,(Table2[Company Name]=InvoicingForm!B5),1)*IF(B7<>0,(Table2[Customer Name]=InvoicingForm!B7),1)*IF(B9<>0,(Table2[Invoice Number]=B9),1)*IF(InvoicingForm!B11<>0,Table2[Category]=InvoicingForm!B11,1)*IF(B13<>0,Table2[Paid Amount]=InvoicingForm!B13,1)*Table2[Invoice Amount])

Please Login or Register  to view this content.

If the invoice number is known nothing else is needed

I would suggest to setup a payment file so the same adding up could be set

this could be added to the paid cell

Please Login or Register  to view this content.

RCM,
Good point about the invoice number, but having it can serve as an error check since the InvoicingForm isn't set up to auto-populate based on the invoice. If ABC Company is listed as well as an invoice for another company, it would return 0 because it's the wrong invoice for that company.
Also, nice formula - I wouldn't have thought of doing it that way. I would suggest using the table column names instead of the D:D so it's not searching the million+ empty rows.

Thank you both for your inputs. I've actually tried the if statement rcm provided before I came to this forum, but it didn't give me what I wanted. For example, say ABC Company as a whole was invoiced $95,000. However, if the user selects Light Bulb Installation as a Category to make payment to, the total invoiced amount should drop from $95k to $50k. I've updated rcm's formula to include category as well:

Please Login or Register  to view this content.

With this formula, I don't think it captures the anomalies as I mentioned earlier. For example, if I only had Company Name and Category selected, it wouldn't add it up since the formula doesn't capture it. Please let me know if there is a formula that captures the anomalies as well. If there is no better way to do this, please tell me that I'm crazy.

Thank you again for your help!!!

Dang it...I forgot to do the ctrl + shift + enter for your array formula, jeffr27. Your formula was exactly what I was looking for. Thank you for your help!! Just need to figure out how to do it for the Total Paid amount. Thank you both for your help!!!! I will mark this as solved.

Thank you again!!!!