Forum Discussion

SoyAllenChiu's avatar
SoyAllenChiu
Copper Contributor
Sep 11, 2023

HELP WITH FORMULA | PLEASE HELP

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 t...
  • NikolinoDE's avatar
    Sep 11, 2023

    SoyAllenChiu 

    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!

    Hope this will help you.

    Was the answer useful? Mark them as helpful and like it!

    This will help all forum participants.

Resources