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)
The FILTER function is one you should be able to adapt on your own. I gave you a hyperlink in one of my earlier messages. Here it is again. And there's this YouTube video that Microsoft used five years ago when they first introduced the function. It was my introduction, and is well worth watching.
Here, though, is the basic syntax
=FILTER(RangeYouWantToSee,(Criterion 1)*(Criterion 2),ShowIfNoRows)
The asterisk between the two criteria functions as an AND. And the criteria should refer to columns in the same table as the range--in your case--being SUMmed.
If it's a case of two criteria but you want an OR between them it would look like this
=FILTER(RangeYouWantToSee,(Criterion 1)+(Criterion 2),ShowIfNoRows)
Why Microsoft chose to represent AND with an asterisk and OR with a plus sign, I can't say.
What I'd recommend is playing with FILTER by itself, before you surround it with SUM. What you'll see is that FILTER is what's called a Dynamic Array function, and that means that, used alone, it will produce an array--essentially multiple rows, all the rows that satisfy the criteria. So it makes more sense to use it when you have a more complete database and want the sum of multiple rows, all the rows that are (for example) pertaining to "Client 1" and "Payments." If Client 1 has made 15 payments, FILTER by itself (given the criteria "Client='Client 1'" and "Payments='Paid'") you'd get an answer with 15 rows. To get that down to one row of the total, you'd write it as =SUM(FILTER(......)) and then it sums that first column.
And all this is really why I'd recommend watching the YouTube video before even playing with it.
Now, now that I've said all that, what I'd really like to see you do is re-design your database. I'm retired now, but before I retired I had spent some time as the director of the HR and pay-related database for a major corporation. In the process of fulfilling that responsibility, I learned a lot about database design. The way you're approaching this--by having separate columns for separate types of expense--raises a red flag in my mind. That approach may make sense when you're working with paper ledger sheets, but you're not; you're working with Excel. Excel has wonderful capabilities at parsing a well-designed database, extracting relevant data where you've differentiated whether a given row refers to income or an expense, or a type of expense....doing all of that in a column labeled, possibly, "Category" See the attached example as a demonstration of concept.