08-03-2020 02:32 AM
08-03-2020 02:32 AM
the attachement picture shows my problem.
I want a function to column b that calculates the last days value and adds sales from h column if the corresponding date, in A column, is in the G column. So for example B4 should get the value from B3 and H4 (15€) and add them together.
Unfortunately I cannot use VLOOKUP in this context as in the real example, the data is not structured for that.
Thank you so much for the one who can help me.
08-03-2020 03:26 AM
@Justme123 Perhaps this is what you need in B2 and copy it down.
The attached file contains an example based on your picture.
08-03-2020 04:07 AMSolution
First of all, thank you for your quick reply. You're already a livesaver. Unfortunately I (think I) can't use VLOOKUP as the "sum" column is before the "dates" column. I also have two more conditions I need to fulfill. I've attached a Picture for reference.
The conditions are these: Type 1 should be A and type 2 should be 1.
08-03-2020 04:37 AM
@Justme123 Not sure I follow what you want with the types. VLOOKUP works just fine with the structure in your examples. If I'm mistaken, yu can use XLOOKUP in case you are a modern version of Excel.
08-03-2020 04:53 AM
Oh sorry for being confusing.
The types just means that those are additional conditions that need to be fulfilld to include the sales for that specific day.
In the Picture, which I've posted under my previous reply, I would love the formula, in B "Sum" column, exclude the "23 €" sales from 17.8.2020 as its type 1 and type 2 are wrong.
Can you post an example in this situation?
But thank you very much.
08-03-2020 05:44 AM
I'm not sure, because the translation is not the best, nor my English either, send you a small solution.
The formula is in column B.
If you find this helpful, please mark it as "Best Answer" and as Like (click thumbs up), it will be beneficial to more Community members reading here.
I know I don't know anything (Socrates)
08-03-2020 06:25 AM
Hey, sorry this is not what I'm looking for. I think you made a mistake because in your file, there's only one cell that has a value.
Please reference to the other answers, to see what I want to do.
Thank you though regardless!
08-03-2020 06:42 AM
@Justme123 Perhaps like int he attached file, provided you are willing to accept the use of a few "helper" cells (shaded green). Just to make it more transparent.
08-04-2020 11:57 PM
Hey, really sorry for late reply.
Your provided solution doesnt work for my situation but that's because I wasnt clear enough on my situation. I'm sorry for that. In the "Final example" attachment you can see how my data is constructed. As you can see from here, the vlookup function wouldn't work because the date of expected arrival is not on the leftside of the data-table we would like to index for.
Again there's two conditions which first one (E-column) is that it needs to be type 1 and the second condition (I-column) is that it needs to be type A.
Again I would love to get a formula that tells me in the c-column, that how much stuff is in storage in each day.
I unfortunately don't have access to xlookup but I can use use of helper cells.
08-05-2020 01:26 AM
@Justme123 Rather than uploading a picture, why not upload your file, or at least the relevant portion of it.That save me having to recreate the schedule
08-05-2020 01:48 AM
Sorry for bothering but thank you, I got it now!
I should have posted an example file, sorry for that.
Have a great week!