3

I'm using a SUMIFS function to get values from a column in another table depending on if they have a certain string in a corresponding memo column. I'd like to be able to check for a few variations on the string, i.e.

SUMIFS(Amount-column, Memo-column, "this-string*"||"that-string*")

Anyone know how to do this?

2

Compounding SUMIF statements with SUM will work as well without chewing up an intermediate results column. Like so:

SUM(SUMIF(memo-column,"this-string*",amount-column),
    SUMIF(memo-column,"that-string*",amount-column))
2

Sorry to bring bad news, but there simply is no suitable formula, in-place ORing, or list comparison of that type in Numbers.

Of the common workarounds for similar spreadsheet problems, I find the "helper column" approach to be the most straightforward and maintainable. Create a new column to calculate whether or not that row should count in the later summing. The helper column can then be the condition source in your sumif formula, and hidden.

For example, helper-column cell D2 could be:
OR( IFERROR(SEARCH("This*",C2),FALSE) , IFERROR(SEARCH("That*",C2),FALSE) )
and after populating that down the rows, your count formula could be:
SUMIF(helper-column,helper-column,Amount-column)

screenshot of the example formulas in action

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .