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.
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.
Thanks so much, Mathetes, for your quicky reply! I'll try it tomorrow and let you know what happens.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.
