12-14-2018 05:24 AM
12-14-2018 05:24 AM
I've been looking for a solution for this function problem I have and I hope someone can help me out? I'm making a sheet where we keep track of different kinds of money flow during an event. But it's kind of a difficult formula for me.
How it should work (see attachments):
To make it more clear: every time we take money from the vault (kluisje) and give it to the entrance (inkom), catering 1 or catering 2, I want it to automatically make a list of all the money that has left the vault so I don't have to fill this out manually at the end of the day.
Can anyone help me out?
12-14-2018 06:02 AM
Are those Catering 1 and Catering 2 in different files? Do you need to copy data from one file to another file? Or are these in the same workheet? If yes have you tried using IF formula?
For column M:
For Column N:
12-14-2018 06:41 AM
All are in the same worksheet. I have tried the IF formula but I don't know how to make sure that all data that matches with value "kluisje" from all 3 subjects (inkom, cat1 and cat2) can be copied underneath each other in column M...
12-14-2018 09:05 AM
if they are not in the same table all mixed up you do not need to use a match index formula. so basically a simple if formula will do the trick just be sure to use the correct absolute and mixed reference syntax
12-17-2018 04:15 AM
I tried it with the simple IF formula but this is what I get (picture nr2). It copies the value of the cells but it doesn't paste it underneath each other in the M-column but horizontally at the same row as the other cell.
Also, I'm still not sure how to get all values with the word "kluisje" from rows C6-C20, L6-L20 and C39-C58 copied chronologically underneath each other into the M-column?
12-17-2018 04:30 AM
what do you mean by "underneath"? Do you need to find the total or something? Can you please elobarate the issue a litte bit?
12-17-2018 05:21 AM
In the M-column should appear all amounts that were taken from the vault, so all underneath each other (from M39 - M...) in one list/column.
Every time I put a value in the columns C6-C25, L6-L25 and C39-C58, the values that have the word "kluisje" next to it, should be copied to an 'overview list' into M39 - M...
Basically, we have 3 cash registers (inkom, catering 1 and catering 2) and we keep track every time we go and get money from the vault and put it into one of those three cash registers. We keep track of the money that we put in the registers and write it down in the worksheet in the columns "Inkom" (C6-C25), "Catering 1" (L6-L25) and "Catering 2" (C39-C58) and instead of copying the amount every time there is money that came from the vault (kluisje), I want it to be copied automatically into an overview list "kluisje" (M39 - M...). In the end I want to easily have an overview of all the money that come from the vault and was put into the cash registers and use the SUM formula to have a total indeed.
Am I making it more or less understandable like this?
12-17-2018 10:19 AM
so as I understand right you want to have a sum of all the values that has "kluisje" in corresponding column you can use sumif or sumifs. If Iunderstand right.
12-18-2018 04:32 AM
I tried this: =SUM.IF(E6:E25;N6:N25;E39:E58 = "kluisje"; C6:C25;L6:L25;C39:C58)
But I get an error message. How do I use this formula on all the columns to get the total sum?