# SUMIF Criteria Between Dates

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?

• Could you share some sample data with us to fix the issue in better way !! ☺ – Rajesh S Sep 18 '18 at 11:10
• Your 2nd formula should written like this, = `SUMIFS(Ordering!\$P\$11:\$P\$365,">=01/04/2018 ,Ordering!\$P\$11:\$P\$365,<=30/04/2018",Ordering!\$M\$11:\$M\$365)` – Rajesh S Sep 18 '18 at 11:43
• And, should you ever want to use `AND` in the future, the proper syntax is `AND(Condition1, Condition2, Condition3,...)` – cybernetic.nomad Sep 18 '18 at 12:32
• @RajeshS SUMIFS order is different. The Sum range should be first not last: `=SUMIFS(Ordering!\$M\$11:\$M\$365,Ordering!\$P\$11:\$P\$365,">=01/04/2018 ,Ordering!\$P\$11:\$P\$365,<=30/04/2018")` – Scott Craner Sep 18 '18 at 12:44
• @ScottCraner,, Yes U are write ,, it's my mistake. Readers plzz read it like this,,, `=SUMIFS(Ordering!\$M\$11:\$M\$365,Ordering!\$P\$11:\$P\$365,">=01/04/2018 ,Ordering!\$P\$11:\$P\$365,<=30/04/2018")`. Basically my prime reason was to alerts OP use `SUMIFS` instead of `SUMIF`. – Rajesh S Sep 18 '18 at 13:34

you can use Sumproduct formula:

``````=SUMPRODUCT((MONTH(A2:A10)=4)*(MONTH(A2:A10)=4), (B2:B10))
``````
• A2:A10 is a range of your dates;
• 4 is number of month (april);
• B2:B10 is a range where your prices are
• This formula will work fine until the Dates are between `01/04/2018` and `30/04/2018` but when its change to `01/04/2018` and `17/04/2018` will create problem. My suggestion is better use the `Dates` instead of `Month`. ☺ – Rajesh S Sep 18 '18 at 13:57
• of course dates will work better, but the question was "for all dates within a given month?" – Bes Gh Sep 18 '18 at 14:04

### Hardcoding the formula

``````=SUMIFS(B2:B10,A2:A10,">=01/04/18",A2:A10,"<=30/04/18")
``````

### Option 1 - Manually Entering Date into Formula

``````=SUMIFS(B2:B10,A2:A10,">="&DATE(2018,4,1),A2:A10,"<="&DATE(2018,4,30))
``````

### Option 2 - Referencing a date in a cell

``````Start Date in G1
End Date in G2

=SUMIFS(B2:B10,A2:A10,">="&\$G\$1,A2:A10,"<="&\$G\$2)
``````

### Option 3 - Returning a 1 month period based the month in a cell reference

``````Date reference in G1

=SUMIFS(B2:B10,A2:A10,">="&EOMONTH(\$G\$1,-1)+1,A2:A10,"<="&EOMONTH(\$G\$1,0))
``````
• updated Option 3 formula. not sure what was running through my head when I typed it out the first time. – Forward Ed Oct 9 '18 at 16:17