Formula for closing balance

[Goodvibe]

File updated

Original Post:

I am trying to find a formula to know the closing balance for each accounts in the tab "Épargnes et Inv." (Column G)

What the formula would do is to update the closing balance each time there is a new data, whether a transaction made (deposit or withdrawal which the amount are in Column "D") or a Profit/Loss on the annual report. I linked a file to give you a better idea of what I look for and in which column the formula will be entered.

Thanks in advance for any feedback, I'm open to every suggestion !

[AliGW]

And what value do you expect to be returned by the formula in G14? What is the result you are aiming for (the actual value, not a description of it)?

[Glenn Kennedy]

What was wrong with the formula that you had in some parts of the column, namely:

=SUMIFS(D$14:[@AMOUNT],C$14:[@ACCOUNT],[@ACCOUNT])

Strictly speaking, SUMIFS is not needed, as there's only one IF and I don't like structured references a lot, so I'd use:

=SUMIF(C$14:C14,C14,D$14:D14)

But either way, same answer. You may need to use ; as the separator.

[Goodvibe]

Updated the file

[AliGW]

And your response to Glenn's suggestion is???

[Goodvibe]

Hi AliGW!

I have updated the file with the expected results for last month and this month. I updated the file twice (one for you and one for Glenn but I thought it would've update only on the original post but the file is shown in the original post #1 and the other in comment #4 (either one have the information you are looking for ). Still trying to comprehend how the forum works. Thanks for helping me out .

[AliGW]

OK - how about:

=INDEX($D7:$AM7,MATCH(EOMONTH(TODAY(),-2)+1,$D$6:$AM$6,0))

and:

=INDEX($D7:$AM7,MATCH(EOMONTH(TODAY(),-1)+1,$D$6:$AM$6,0))

[Goodvibe]

Hi Glenn!

The SUMIFS and the values shown were good at the time that I had only the AMOUNT column (column D) but few days later, I've add the PROFIT/LOSS column (column F) so now I wasn't able to add in the the PROFIT/LOSS in the SUMIFS formula (wanted to sum AMOUNT and PROFIT/LOSS together in the SUM RANGE but it gives me a VALUE error). I've updated the file to let you know what would be the expected result (example row is highlited in black). Let me know if you need more detail/infos. Thanks for taking the time to try to solve the issue!

[Glenn Kennedy]

Why did you replace the file and delete the requirement at Post 1??? Talk about causing confusion!!

Try this:
=SUMPRODUCT((C$14:C14=C14)*(D$14:D14+F$14:F14))

copied down

[AliGW]

OK - looks like the cells highlighted in the file I looked at are not the ones you are asking about.

I will just move on - nothing to see here.

Oh, and I reinstated the text of your opening post - don't delete messages like that again.

By the way, let me know, please, if the solutions I offered are of any use.

[Goodvibe]

Perfect! Exactly what I was looking for! Thanks AliGW !

P.S. By curiosity, at the end of the EOMONTH part of the first formula ...-2)+1?... , why can't we just write -1 to give us the last month instead of -2)+1?

[AliGW]

Because TODAY()-1 is not always going to be the first of the month. Your month row are all first of the month, so that's what we need for the match.

EOMONTH(TODAY(),-2) is 30 November 2020, then +1 is 01 December 2020. You can work the other one out from that.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[Goodvibe]

Excellent !! It works! Thanks a lot for the help Glenn!

I understand the confusion and maybe the irritation, sorry about that! It wasn't intentional regarding the deleted requirement post :S. Still trying to figure out how the forum operates. It's because I don't know how to attach a file in the reply section instead of updating it in the original post. Don't even know how my duplicate file ended up outside of the original post :S??? If you or anybody can enlighten me on this one, it would help me to avoid further confusions.

[AliGW]

Have we answered different questions??? I am very confused.

Please see post #12 with instructions about how to mark this as solved.

[Goodvibe]

Aaaah ok makes sense!! Thanks for taking the time to explain !

Thanks for reminding me regarding the SOLVED status and the add reputation when someone helped me (will get use to how the forum works over time ). Have a great day of evening AliGW!

[Goodvibe]

Hi AliGW!

Just updated the status of the thread tool to "SOLVED"

[AliGW]

Thank you!

[Goodvibe]

You're welcome!