How to copy a cell's value IF...

Occasional Contributor

Hi everyone,


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):


  1. If E39 has a value with the word "kluisje" then the value of C39 should be copied into M39 and the value of A37 should be copied into N39.
    => this should work for all cells from E39 until E58, whenever there's a cell with value "kluisje".

  2. BUT... apart from "Catering 2", I also have "Inkom" and "Catering 1" of which I want the values to be copied into N39-N... whenever there's a value with the word "kluisje".


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? 





10 Replies

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:



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...


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


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?


what do you mean by "underneath"? Do you need to find the total or something? Can you please elobarate the issue a litte bit?


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?




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. 


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?




can you please share the file? 


I have fixed it in another way and it is working now. Thank you for your help!