Problems with refreshing data in chart

Hi everyone.

I am new to libreOffice and having issues with charts.
In attachement you can find the file I am having problems with. The file contains me and my girlfriends monthly expenses. Everything works fine like we want it but the only problem is the chart.

When I copy the sheet 0912 to 1112 and I change the data. The graph or chart doesn’t update to the changed data. Is there anyone who can explain me in understandable language what to do?

Many thanks in advance!

[http://www.pietergoris.be/Kostenstaat_bis.ods]

Because the graph have their own data, it is not reference to the spreadsheet cells.
Maybe easy delete it and create a new one.

I had a look at your spread sheet and saw that you mixed cells with number and text and add all. this cannot work.
eg
d6 contains =SUMPRODUCT($A$68:$A$165=“Pieter/Jessi”,$B$68:$B$165=$A$6,$H$68:$H$165) and these expressions go to cells
a6 is text
a68 to a165 this range is all text
h68 to a 165 this range contains number

I also feel that SUMPRODUCT is not the right function. Depending on what you want to do you might need to work with a condition like IF or SUMIF

To me it seems you need to build you spread sheet new. However, as I don’t know what you want to do I cannot tell you more right now.

Hey ROSt52 thanks already for your help. I want to excuse me for my newbie level on this but I hardly ever use it. I have to make it because they are gonna help me (government instances) with giving structure to my finances since I suffer a sort of ADHD.

I hope you can help me changing some things in the current spreadsheet since I already had to do a lot of effort making this one. There already went a whole lot of my spare time in it.

The things I want to do on pages 0912 and 1112 is the next.
Cells A69 to H150 contain monthly expenses. They all have the name of the expender, girlfriend or me. And they have a category. Want I want to do on top. Is making an overview of the cost per category.
Sorry that the spreadsheet is in Dutch by the way. Because we need to see who makes the most expense per category I wanted to split things up. If a certain amount for example is spent by Jessi AND falls under a certain category I want to sum it.

And the chart should give a graphical overview on the information in the ‘table’ on top. Thing is we need to do this each month. So 1212, 0113 and so on (month, year) is the spreadsheet name.
Is there anyway you can help me editing my spreadsheet with the information above or do you need more information. Because I am not familiar with al these functions. Just tried finding it out using the internet but looking at your comment I used wrong functions.

Many many thanks for your help.
P.

Little extra comment. Also I which I can just copy and edit the data and graph to a new spreadsheet each month. Without create a new chart each time and edit the look and feel from it.
In the chart I wish to see the total per category and who spent amount of % spent within that category if possible

@Pieter1,

I spreek ook en beetje nederlands; maar mischien zeg ik beter vlaams…

Now I understand a bit what you want to do
You want to enter data in rows 68 - 165 and the formulas on top of the sheet should summarize in names and categories.
The summary should be transfered also into a chart (which is a very good idea)
You want to have one sheet for each month.

I will start with the end.
Once you have one sheet done, you can copy sheets, right click on the tab

As for the formulas, I don’t have the time currently to find out how exactly they have to look like. There is a SUMIF function, but I don’t know if this formula can handle 2 conditions. SUMPRODUCT is definitely wrong.
If SUMIF doesn’t work, try IF. maybe you better introduce a colum which calculates the whole thing in steps. That’s typically the way build up complex formulas. Later you always can put them together again.
Look into the help file.
Start simple and once it works try use more complex ways to build formulas.

Have fun!

Hi ROSt52,

I followed your lead and changed in the file the totals to SUMIF’s.
That works fine. Only question now is how to add up the second criteria. I tried working with AND in the formula but I can’t figure it out. Can you have a quick look please into cell D19 on worksheet 1112.

I uploaded the changed file under the same download link.
What I want is something like in D19 but can’t figure out how to build the function properly. First I tried with an IF in front of the SUMIF. Not a good result either.

Thanks for your help in advance since I can use it.

Greets.
Pieter.

@Peter, could you solve your spreadsheet problem already?

@PieterGoris, Please let us know if you still are interested in this problem. If not, we’ll close-out this question.

Thanks!