I am trying to make a formula that will allow the result to be based on what input. Example, the formula will check if one of these terms is filled in "INTEREST PAYMENT" OR "REWARD". BAsed on those terms, the formula should switch between these 2 options | =(IF(ISNUMBER(SEARCH("INTEREST PAYMENT",B157)),(SUM(K157+O157+Q157)),(0))) | or | =(IF(ISNUMBER(SEARCH("REWARD",B157)),(SUM(M157+O157+Q157)),(0)))

3 Replies

=IF(ISNUMBER(SEARCH("INTEREST PAYMENT",B157)),

SUM(K157+O157+Q157),

IF(ISNUMBER(SEARCH("REWARD",B157)),

SUM(M157+O157+Q157),0))

This works in my Excel for the web sheet.

Can't really test your formula and there may be better/other ways, but perhaps this is what you want.

``=IF(OR(ISNUMBER(SEARCH("INTEREST PAYMENT",B157)),ISNUMBER(SEARCH("REWARD",B157))),M157+O157+Q157,0)``

or in format that is easier to read:

``````=IF(
OR(
ISNUMBER(SEARCH("INTEREST PAYMENT", B157)),
ISNUMBER(SEARCH("REWARD", B157))
),
M157 + O157 + Q157,
0
)``````

Hi thank u so much for the replies. I did all those steps before, but none of them results good. If you can see on the next photo below, in column "credit amount" there should be a formula that will allow the 2 formulas I made to be used based on what is filled in column "Description".

Formula 1 works like this:

=(IF(ISNUMBER(SEARCH("INTEREST PAYMENT",B157)),(SUM(K157+O157+Q157)),(0)))

That means: If in Description (B157) IS FILLED INTEREST PAYMENT, it will sum K157, O157, Q157.

Formula 2 works the same as 1 but changes based on the argument/term:

=(IF(ISNUMBER(SEARCH("REWARD",B157)),(SUM(M157+O157+Q157)),(0)))

That means: If in Description (B157) IS FILLED REWARD, it will sum M157, O157, Q157.

As I can finally explain, these terms "INTEREST PAYMENT" or "REWARD" will decide which of the following formulas 1 or 2 should be used to result in the column "CREDIT AMOUNT" 