0

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
  • 1
    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
  • 1
    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
  • 1
    @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
1

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
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.