Suming across multiple sheets based on condition

bujalarab · October 15, 2021, 6:46am

Hi everyone,

I have looked for a solution for my issue and did not managed to find ananswer.
I have a file with 52 sheets for income. In each sheet I can choose with a scroll down menu the type of income, which will calculate the amount of taxes I have to pay.
I would like to have a sheet that will calculate the totals and give results for each type of income.
How can I sum in this case across multiple sheets based on the type of income in each sheet?

I have attached a file to be more clear.
Testing.xlsx (16.5 KB)

GrahamLees · October 15, 2021, 12:07pm

are you using LO Calc or Excel? There may be differences between the solutions in the applications

bujalarab · October 15, 2021, 6:58pm

I am using LO Calc. The initial file was made some years back in Excel and have not changed the extention since starting using OL.

gregors15 · October 15, 2021, 11:03am

Hi, take a look at the attached sheet, I use a similar type of sheet for but for monthly totals and tax calculations. If it’s not exactly what you want, it might point you in the right direction. To make the sheet work you need to enter the weekly sheet name in row 1 once you have created it. Let us know if it helps.

Created using Windows10 LO 7.2.1.2.
LOQ20211015.xlsx (20.9 KB)

bujalarab · October 15, 2021, 7:13pm

Hi Gregor,

Thank you to make the time to put together this file for me.
I think my initial post was not quite clear. Just realised that. The suming should be done based on the condition in cell B1 of each sheet. That condition tells me what type of income I have made .
I.E if it is CIS it means that I payed tax for CIS type of work. The net value of income is calculated based on that condition though both type of tax values appear in the sheet.
I hope this time I explained it better.
I appreciate your effort and it is somehow in the direction I need it.

LeroyG · October 15, 2021, 10:10pm

Put the “scroll down menu” in the TOTAL sheet, and in B1 of the other sheets =$TOTAL.D1.
So, when you change D1 value in TOTAL, all other sheets change the value accordingly.

bujalarab · October 16, 2021, 6:32am

Thank you all for the help.
@gregors15 after playing more with you suggestion I come to the conlclusion it is suitable for my need. It also give me the option to use it if both type of incomes happen during the same week.