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.
I think it would be something like
=SUMIF('List of Transactions'!G3:G373,
AND(
OR('List of Transactions'!K3:K373,"R",
'List of Transactions'!I3:I373,"R"
),
'List of Transactions'!G3:G373,">"&0)
)
Not sure you need that last clause about values in G being greater than zero, unless there are negative values that you are excluding.
Note: I've not been able to test that re-formulation since you didn't share the spreadsheet itself, but that would be the kind of syntax if you're staying with SUMIF
Another way would be using =SUM(FILTER(......)) and stating the criteria within the FILTER function.
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!
Here's a very simple example of the kind of thing I was referring to as possible in my last gratuitous, unsolicited comments. Feel free to totally ignore if it doesn't fit with your needs.
- Thanks so much, Mathetes, for all your attention and input to this! I hope to have a chance to take a look at your suggestions later this evening.
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.
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.
I would be very grateful if you could provide guidance on how I would use FILTER in two additional formulas, if you would be so kind.
Also on my P&L sheet are calculations for nonreimbursable expenses and client reimbursable expenses. For each I need to also include the function of summing only when there is an R in either Column I or Column K.
(Screenshots below of an example of the current formula for each)
As a reminder... the columns on the List of Transactions tab (screenshot)
Thanks so much, Mathetes, for your quicky reply! I'll try it tomorrow and let you know what happens.
