Excel - IF Function Problem - Expert Solution

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
Excelchat Expert 07/10/2018 - 09:30
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
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
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
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
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
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
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
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
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
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
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
Excelchat Expert 07/10/2018 - 10:09
Let me try to solve it
User 07/10/2018 - 10:10
Excelchat Expert 07/10/2018 - 10:23
April is missed?
User 07/10/2018 - 10:23
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
User 07/10/2018 - 10:24
Excelchat Expert 07/10/2018 - 10:24
or April?
User 07/10/2018 - 10:24
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
User 07/10/2018 - 10:26
User 07/10/2018 - 10:28
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
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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc