formula to count number of orders by month

[steve400243]

Hello Forum, I have a spreadsheet with a column of end dates in column "R". I need the formula to count all orders from column "D" by month. Here is what I have that is not working - A4 is "JAN-18". H4 is the desired cell for the total

=SUMIFS('Pricing Log'!D:D,'Pricing Log'!$R:$R,">="&$A4,'Pricing Log'!$R:$R,"<=($A4,0))

 

[steve400243]

Thanks for the help Gerald H, appreciate your time. Joe4's formula addition worked.

 

[Joe4]

You are welcome.

I wondered what you were trying to do with: ($A4,0)
because that by itself without any function doesn't really make any sense.

 

[steve400243]

Thanks Joe4, any idea why it would not be counting all the data in that column? there are currently 96 in that column and it is only counting 92? maybe a date error in one of the rows?

 

[Joe4]

Thanks Joe4, any idea why it would not be counting all the data in that column? there are currently 96 in that column and it is only counting 92? maybe a date error in one of the rows?

Yes, it could be due to the time component in column R.
If there is no time component on a date, 0:00 is assumed.
So if you have any value in column R that are on the last day of the month, and have a time component, they will be after 0:00, so will fall outside of our range.
So we need to change it to say "less than" 0:00 of the NEXT day, like this:

=SUMIFS('Pricing Log'!D:D,'Pricing Log'!$R:$R,">="&$A4,'Pricing Log'!$R:$R,"[COLOR=#ff0000]<"&EOMONTH($A4,0)+1[/COLOR])

 

[steve400243]

Thats it, Thank you again for your help! Have a great Day!!