SOLVED

Copper Contributor

I am trying to make a formula that will allow the result to be based on cell 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)))

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"

3 Replies
best response confirmed by SoyAllenChiu (Copper Contributor)
Solution

It sounds like you want to create a formula in Excel that checks the value in the "Description" column and then uses different calculations based on whether the description contains "INTEREST PAYMENT" or "REWARD."

You can achieve this by using an IF function in combination with SEARCH. Here is the formula:

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

Let me break down how this formula works:

• ISNUMBER(SEARCH("INTEREST PAYMENT", B157)): This checks if the text "INTEREST PAYMENT" is found in cell B157. If it is found, it returns TRUE; otherwise, it returns FALSE.
• IF(ISNUMBER(SEARCH("INTEREST PAYMENT", B157)), SUM(K157, O157, Q157), ...): If "INTEREST PAYMENT" is found, it calculates the sum of K157, O157, and Q157.
• IF(ISNUMBER(SEARCH("REWARD", B157)), SUM(M157, O157, Q157), 0): If "INTEREST PAYMENT" is not found, it checks if "REWARD" is found in cell B157. If it is found, it calculates the sum of M157, O157, and Q157.
• 0: If neither "INTEREST PAYMENT" nor "REWARD" is found, it returns 0.

So, this formula checks for the presence of "INTEREST PAYMENT" and "REWARD" in cell B157 and calculates the sum accordingly. You can copy this formula down to apply it to other rows in the "CREDIT AMOUNT" column based on the values in the "Description" column.

The text, steps and functions were created with the help of AI.

My answers are voluntary and without guarantee!