Log In
Register
Home
Active Member
Excel Tables
July 30, 2020
Offline
Hello,
I am working on enhancing a formula. Currently, I utilize sumproduct and nested if statements to accomplish bringing in data from an external workbook (same workbook for testing purposes). This formula ends up being ~5000 characters per cell multiplied by 240 cells.
Hoping to make this formula more streamlined and dynamic with DSUM nested with another function. DSUM would allow for users to select between 5 combinations of divisions, cost centers, and projects. I was able to get DSUM to work perfectly, but having trouble making it dynamic for each expense category using one criteria table.
End result would be 2 criteria tables (one for sending expense and another for receiving) that could be utilized for all the expense categories on my P&L.
Range("G7:G38") contain the formula being worked on.
Any guidance would be much appreciated.
Thank you,
Matt
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Do you have a sample file you can upload?
Br,
Anders
Active Member
Excel Tables
July 30, 2020
Offline
Sure thing. Just attached. sorry for the delay. I kept getting an error until I sent the sample file to my home computer.
VIP
Trusted Members
December 7, 2016
Offline
Hello,
I am afraid that my reply won't contain much help, but to get a sum per category using DSUM you need to add the category/-ies as a criteria, meaning that you either need more than two criteria tables or need to add more rows for each table. Perhaps another approach is to create a Pivot Table instead. Or look for a VBA solution.
Br,
Anders
1 Guest(s)
