question

ZeynepBARAN-8395 avatar image
0 Votes"
ZeynepBARAN-8395 asked emilyhua-msft commented

Determine total value based on condition in cell in Excel

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-formsoffice-excel-itprooffice-deploymentoffice-scripts-excel-dev
excel1.png (12.5 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

emilyhua-msft avatar image
0 Votes"
emilyhua-msft answered emilyhua-msft commented

@ZeynepBARAN-8395

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.



image.png (25.5 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@ZeynepBARAN-8395
Please check whether my suggestion is helpful.
Any questions, you may post back.

0 Votes 0 ·