Forum Discussion
Excel on Mac - Evolving a Formula
See what you think of this approach. I changed your database to a table, which has the advantage of "automatically" adjusting the formulas, as you add rows of data, to accommodate the number of active rows. I also cleared out the colored columns below the active data. Those increase, unnecessarily, the size of the file.
And I used the FILTER and SUM function, as you'll see, rather than SUMIF. That (IMHO) is cleaner. The hyperlink there will give you more information on how to use FILTER.
Hello again, Mathetes,
I've attached a spreadsheet with the formula you suggested. It's not returning the expected value. I would be very grateful if you would take a look.
And, you're right, I've realized I don't need the last clause, as an R in either Column K or Column I will be only for positive numbers. You'll see B6 is the new formula without that clause, which is the formula I anticipate I'll need.
Thanks again!
And, you're right, I've realized I don't need the last clause, as an R in either Column K or Column I will be only for positive numbers. You'll see B6 is the new formula without that clause, which is the formula I anticipate I'll need.
Here's an unsolicited, and therefore not needing to be heeded, piece of advice (or suggestion). I don't know the bigger picture here, obviously, but just seeing a few "skeletal" details, it strikes me that there might be better ways to organize the data. For example, rather than combining in one cell things like "Client 1, invoice" and "Client 1, payment" I would be more inclined if it were my database, to separate those two pieces of information. The "who" is one piece of information. The nature of the transaction is another. And you could then summarize things on a dashboard or other profit/loss sheet, very easily displaying total invoices, total payments by customer, by month, or whatever. Especially if people don't always pay the full amount, i.e., if some pay off their debt on a monthly basis or whatever--which I should think might happen, you'd want to see a more granular kind of summary.
(The Pivot Table could come in handy for summarizing too, if your data were organized slightly differently.)
And I appreciate your additional advice! This is my first year of moving my accounting to Excel from an online accounting software with which I was very frustrated and completely unsatisfied. You advice makes me realize there's a world of Excel possibilities. Once I make it through this year with an Excel proof of concept, I might evolve the sophistication and elegance of what I have.