Forum Discussion
Excel on Mac - Evolving a Formula
Hi,
I have a question that I'm sure someone out there can answer.
The following formula has worked great, but I need to evolve it: =SUMIFS('List of Transactions'!G3:G373,'List of Transactions'!K3:K373,"R",'List of Transactions'!G3:G373,">"&0)
Here's what I need the formula to do, with the functionality I need to add in bold:
- Sum the values in Column G IF
- Column K is R
- OR Column I is R
- And the value in Column G is > O
Thanks in advance for your help!
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.
13 Replies
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!
Thanks so much, Mathetes, for your quicky reply! I'll try it tomorrow and let you know what happens.
