I have sheet Costs, and Dates the cost was raised, date cost was actually received. The cost is formatted as Currency. Planned Recieve Date is a drop down select, and Actual Goods receipt is formatted as below e.g.
Cost | Planned Recieve Date | Actual Goods Receipt $XYZ | Apr-18 | 11 April 2018 $XYZ | Apr-18 | 24 April 2018 $XYZ | Apr-18 | 28 April 2018
What I am trying to do, is see a sum of all costs EXPECTED to be raised in a given month, and how much was ACTUALLY recieved in that given month. The Costs with dates are on one sheet "Ordering" and the formula i need is to go on another sheet.
Expected costs uses the column Planned reciept date. My formula is:
= SUMIF(Ordering!$L$11:$L$365, "Apr-18", Ordering!$E$11:$E$365)
What is giving me trouble is the Ammount actually recieved, I can't seem to figure out the criteria correctly. This uses the 'Actual Good Receipt" column, which has a formatted date. The cell displays 24 April 2018, and has 24/04/2018 in te cell editor box. The formula I've used is:
= SUMIF(Ordering!$P$11:$P$365,">=01/04/2018 AND <=30/04/2018",Ordering!$M$11:$M$365)
This gives me a value BELOW what I expect, and im missing no costs. (only have 3 for the omnth of april.) I have tried multiple different ways of writing my Criteria, and I have used wildcards.
What is the correct way to construct my criteria so that I can see total cost of the ACTUAL RECIEVED for all dates within a given month?