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))

 

[Joe4]

What date is actually in cell A4?
Every valid date entry has a day component associated with it, even if the cell is formatted to only show the month and year.

What do the dates in column D look like?

 

[Gerald Higgins]

You have inconsistent formats in the criteria.

Consider this tweak . . .
=SUMIFS('Pricing Log'!D:D,'Pricing Log'!$R:$R,">="&$A4,'Pricing Log'!$R:$R,"<="&$A4)

But also, it looks like you are testing that the values in column R are BOTH GREATER THAN OR EQUAL TO A4, AND LESS THAN OR EQUAL TO A4, which must generate a TRUE result for every possible value CORRECTION, WHICH CAN NEVER BE TRUE FOR ANY VALUE OTHER THAN AN EXACT MATCH TO A4.

 

[steve400243]

A4 = Jan-18, A5 = FEB- 18, etc

Column D is A number 1,2 etc. referencing the number of quotes for that day. Also Sorry for not mentioning the cell H4 is on another sheet named "DATA"

Column R looks like

1/2/2018 8:07

<tbody>
</tbody>

 

[Gerald Higgins]

Then try

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

Do you understand Joe4's point about the day element of the date ?

 

[Joe4]

A4 = Jan-18, A5 = FEB- 18,

That doesn't answer my question regarding Day....

Can you temporarily change the format of the cells in column A to a date format that includes day (i.e. dd/mm/yy) , and let me know what it shows?

 

[steve400243]

sorry about that, now shows 1-Jan-18

 

[steve400243]

I think so, changed the format in A4 to dd/mmm/yy

 

[Joe4]

I think you are just missing the "EOMONTH" function name in your original formula, i.e.

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

 

[steve400243]

Thank you Joe4, that worked. Didnt even think about it. Appreciate your time.