Question description:
This user has given permission to use the problem statement for this
blog.
I need assistance with an excel formula. I have a 12 month sheet that has a summary page on the 13 page. the monthly totals changes so on the summary page I want to sum the difference - eg. if Jan total is 1500, feb total is 1000, mar total is 800, I want the summary page to show the current amount of 800 when you place the formula to capture all months---showing the diminishing value rather than the sum of all the pages
Solved by E. L. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
07/10/2018 - 09:30
yes
Excelchat Expert
07/10/2018 - 09:30
hi
Excelchat Expert
07/10/2018 - 09:31
How can i help you?
User
07/10/2018 - 09:31
I stated my issue
Excelchat Expert
07/10/2018 - 09:31
may i see the sheet?
User
07/10/2018 - 09:32
hold
User
07/10/2018 - 09:34
[Uploaded an Excel file]
User
07/10/2018 - 09:34
take a look
User
07/10/2018 - 09:35
are you there??
Excelchat Expert
07/10/2018 - 09:35
yes, checking the file
Excelchat Expert
07/10/2018 - 09:36
You want to modify summary sheet
User
07/10/2018 - 09:37
on the summary page, I only need to see the dimishing total- not the total of all pages...since these totals changes by going down. it is showing me a total of all the pages which is incorrect.
User
07/10/2018 - 09:38
since the first sheet is the total of what I owe, when I make payments, I want the summary sheet to display the new total, so when the next month payments are made it shows the updated new totals and so on
Excelchat Expert
07/10/2018 - 09:41
to make sure that i understand what you want. You want cell F6 in summary sheet to show which data from other sheets?
User
07/10/2018 - 09:45
ok- every sheet has a total---currently F6 is the sum of ALL pages- which is not correct. Since each sheet has a total, these totals will change monthly. E.g. Jan total might be 1500, so the summary page will start out showing 1500. However if I make payments in January in the amount of 200, then Feb totals will now show 1300, I need the summary page to now show 1300. If Feb sheet decrease by 200 to 1100, the new summery page total should show 1100 and so on.
User
07/10/2018 - 09:45
The monthly sheets K36 is the cell that shows the new balances.
Excelchat Expert
07/10/2018 - 09:46
where is the cell that show the payment amount?
User
07/10/2018 - 09:47
mistake not K36 but G36
User
07/10/2018 - 09:47
H36
Excelchat Expert
07/10/2018 - 09:49
so in the sheet you sent, jul total is G36 = 3491
Excelchat Expert
07/10/2018 - 09:49
amount you pay is H36 = 825
Excelchat Expert
07/10/2018 - 09:49
right?
User
07/10/2018 - 09:49
payments are G36- however if no payment are made for the month fees are included, so the G36 shows the new totals including the fees
User
07/10/2018 - 09:50
correct
Excelchat Expert
07/10/2018 - 09:51
iif we have jul sheet only, then the summary sheet shall show 3491-825, right?
User
07/10/2018 - 09:51
G36 is the cell that is updated monthly with the new totals---including late fees and new bills totals.
User
07/10/2018 - 09:51
correct
Excelchat Expert
07/10/2018 - 09:52
lets add Aug sheet, G36 is the sum of G11 to G35
User
07/10/2018 - 09:53
yes
User
07/10/2018 - 09:53
column G will always show either an increase or decrease totaling in G36
User
07/10/2018 - 09:54
So that is the diminishing total that needs to show on the summary page
Excelchat Expert
07/10/2018 - 09:54
Yes but i cannot see that they add the value of remaining payment from jul
User
07/10/2018 - 09:55
the current formula on the summary page adds all the sheets together. so if Aug and Sept totals are 1500 each, it will add on the summary page to 3000, however I only owe 1500 in Aug but did not pay in Sept, so the total should remain at 1500 not 3000---hope this helps
User
07/10/2018 - 09:57
if you look at the summary page it shows 12K, I don't owe that much since the total is only about 4.5K showing in Sept....understand?
User
07/10/2018 - 09:58
the monthly sheets are the total--the only difference that show show on the summary is if a total change in the previous month the summary show now show that new total and not the sum of all the pages.
Excelchat Expert
07/10/2018 - 09:59
Lets forget the summary and make it manually then i will automate it
Excelchat Expert
07/10/2018 - 09:59
for jul, from K36, it means that you have to pay 2666.3, right?
Excelchat Expert
07/10/2018 - 10:00
it is G36-H36.
User
07/10/2018 - 10:00
right
Excelchat Expert
07/10/2018 - 10:01
in aug, it means you have to pay 3201.67 include the previous months? this 3201.67 include 2666.3 from jul?
Excelchat Expert
07/10/2018 - 10:01
Or it is the payment for aug only?
User
07/10/2018 - 10:02
the 3201 includes the 2666 correct
Excelchat Expert
07/10/2018 - 10:03
I don't see that but ok, lets continue. in sept. you have to pay 4538 include all previous months. include 3201, right?
User
07/10/2018 - 10:03
right
Excelchat Expert
07/10/2018 - 10:05
lets goto OCT, it has no data in K36 but i think there is a mistake in column g. i think column F must be moved to column G, right?
User
07/10/2018 - 10:06
I was about to say---its not populated yet, there will be data in G that will then populate over to K
User
07/10/2018 - 10:06
it is correct--
User
07/10/2018 - 10:06
my admin has to retrieve the data
Excelchat Expert
07/10/2018 - 10:07
So the sheet is working from jul, aug and sept, right?
User
07/10/2018 - 10:07
yes
User
07/10/2018 - 10:08
they all work but the data that is not yet in G for October will be added on Monday which will produce the information in K that shows the total owed for Oct.
Excelchat Expert
07/10/2018 - 10:08
the right answer for cell F6 in summery sheet is the same as K36 in sept, right?
User
07/10/2018 - 10:09
right-
User
07/10/2018 - 10:09
and when she updates oct- the new total for Oct in K36 should be reflected in the summary F6
Excelchat Expert
07/10/2018 - 10:09
yes
Excelchat Expert
07/10/2018 - 10:09
Let me try to solve it
User
07/10/2018 - 10:10
ok
Excelchat Expert
07/10/2018 - 10:23
April is missed?
User
07/10/2018 - 10:23
ouch
User
07/10/2018 - 10:23
can I add and resend
Excelchat Expert
07/10/2018 - 10:23
to not waste time tell me the name of sheet
Excelchat Expert
07/10/2018 - 10:24
Apr?
User
07/10/2018 - 10:24
yes
Excelchat Expert
07/10/2018 - 10:24
or April?
User
07/10/2018 - 10:24
Apr
Excelchat Expert
07/10/2018 - 10:25
try this formula in cell F10 "=IF(Dec!K36=0,IF(Nov!K36=0,IF(Oct!K36=0,IF(Sept!K36=0,IF(Aug!K36=0,IF(Jul!K36=0,IF(June!K36=0,IF(May!K36=0,IF(Apr!K36=0,IF(Mar!K36=0,IF(Feb!K36=0,IF(Jan!K36=0,TRUE,Jan!K36),Feb!K36),Mar!K36),Apr!K36),May!K36),June!K36),Jul!K36),Aug!K36),Sept!K36),Oct!K36),Nov!K36),Dec!K36)"
User
07/10/2018 - 10:26
ok
User
07/10/2018 - 10:26
checking
User
07/10/2018 - 10:28
solid---works
Excelchat Expert
07/10/2018 - 10:28
and this one in F6: "=IF(Dec!G36=0,IF(Nov!G36=0,IF(Oct!G36=0,IF(Sept!G36=0,IF(Aug!G36=0,IF(Jul!G36=0,IF(June!G36=0,IF(May!G36=0,IF(Apr!G36=0,IF(Mar!G36=0,IF(Feb!G36=0,IF(Jan!G36=0,0,Jan!G36),Feb!G36),Mar!G36),Apr!G36),May!G36),June!G36),Jul!G36),Aug!G36),Sept!G36),Oct!G36),Nov!G36),Dec!G36)"
User
07/10/2018 - 10:29
ok
Excelchat Expert
07/10/2018 - 10:29
and this one in F8: "=IF(Dec!H36=0,IF(Nov!H36=0,IF(Oct!H36=0,IF(Sept!H36=0,IF(Aug!H36=0,IF(Jul!H36=0,IF(June!H36=0,IF(May!H36=0,IF(Apr!H36=0,IF(Mar!H36=0,IF(Feb!H36=0,IF(Jan!H36=0,0,Jan!H36),Feb!H36),Mar!H36),Apr!H36),May!H36),June!H36),Jul!H36),Aug!H36),Sept!H36),Oct!H36),Nov!H36),Dec!H36)"
Excelchat Expert
07/10/2018 - 10:29
for F10, raplace " TRUE" with 0
Excelchat Expert
07/10/2018 - 10:29
it will be
Excelchat Expert
07/10/2018 - 10:30
F10 : =IF(Dec!K36=0,IF(Nov!K36=0,IF(Oct!K36=0,IF(Sept!K36=0,IF(Aug!K36=0,IF(Jul!K36=0,IF(June!K36=0,IF(May!K36=0,IF(Apr!K36=0,IF(Mar!K36=0,IF(Feb!K36=0,IF(Jan!K36=0,0,Jan!K36),Feb!K36),Mar!K36),Apr!K36),May!K36),June!K36),Jul!K36),Aug!K36),Sept!K36),Oct!K36),Nov!K36),Dec!K36)
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
