Sum x most recent occurences

I'm trying to sum the four most recent occurences based on a given critera and then be able to autofill for a large dataset.

Please refer to the attached spreadsheet which will hopefully make the problem more obvious. I have the date in column A, a colour randomly listed in column B and a random figure in column C. In column D, for each row, I want the total figure from column C for the 4 most recent occurences from the date in column A of the colour given in column B. I have manually filled the first 5 rows for column D so it can be seen what I am after. I want to be able to autofill as my real dataset is very large.

Im not gonna lie, I don't really know where to start with this one... any help much appreciated!

Excellent, thank you very much for taking the time to figure that out. I just needed to remove the lock on a couple of things so I could autofill without it summing more recent values for a given row.

There are a couple of functions there I am unfamiliar with so I will take some time to go through your formula and understand it.

I have attached a spreadsheet so you can see how I've used the formula and applied it to all rows

In column E I have manually filled some values showing the sum for the previous colour matching the colour given for that row. I want to next write a formula to do this automatically. I would imagine I will have to use some of the functions you have used in your formula?

Finally, I am trying to work out where I need to enter another IF function in your formula to return the text "N/A" when there are no longer 4 values to sum for a given colour. I.e. at the bottom of the dataset (see what I manually entered at bottom of sheet to see what I am after).

Thanks again