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!
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.