Determine total value based on condition in cell in Excel

Zeynep BARAN 21 Reputation points
2021-09-06T13:30:18.153+00:00

Hello,
I'm trying to make a small application in Excel. For example, 12 payment records are kept in a table. I want it to be deducted from the total amount of money when "paid" is written in the cell next to the received payment in these records. I made a study, but as you will see in the appendix, I cannot maintain it in the same cell. How can I check this?

formule is: =IF($E3="Paid",D14-D3,D14) but I actually want to check it in the cell where the total money is not a single row, it should change automatically according to the value of 12 cells. scrolling down like this is not a solution for me. What can I do?

129671-excel1.png

Windows Forms
Windows Forms
A set of .NET Framework managed libraries for developing graphical user interfaces.
1,828 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,488 questions
Office Management
Office Management
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Management: The act or process of organizing, handling, directing or controlling something.
2,000 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,640 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Emily Hua-MSFT 27,526 Reputation points
    2021-09-07T02:17:48.45+00:00

    @Zeynep BARAN

    Please try the formula =D14-SUMIF(E$3:E$13,"Paid",D$3:D$13), which uses Sumif function to get the Paid Value sum in "Paid" state, then the total amount subtracts this part of it.

    129737-image.png

    Or you may use the formula =SUMIF(E$3:E$13,"Not Paid",D$3:D$13)+SUMIF(E$3:E$13,"Waiting",D$3:D$13) to get the Paid Value sum other than value in Paid status.

    If an Answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.