date format problem and SUMIF() is not working date criteria

Hi all,
i import a file from system and i want to change date column format in different way using custom and other but there is no change in date format, why?
i am using a formula to get item Qty,=SUMIF($A$2:$E$331,G2,$E$2:$E$331) but result is not OK, my july month end of last transaction 27/7/2017 and g2=31/7/2017, July-2017 formula is calculating 27/7/2017 not 31/7/2017. next columns is also required to filled by formula. i am also using SUMIF next columns.
Thanks.

First of all, you need to convert text into date in Column A. This can be achieved by following steps:

Select Column A
Go To Data > Data Tools > Text to Columns > Next > Next >
Column Data Format : Date (DMY) > Finish.

Then put the following Formula in Cell H2 and drag Down:

Please Login or Register  to view this content.

-Given that you will always enter last day of the month in Column G.

Further, It was not clear, how and what you want to achieve in other cells.

If I guess it right, you want to sum all those entries whose first three letters (e.g., MD-) coincide with the heading. For this, use this formulae in Cell I2 and drag across

Please Login or Register  to view this content.

Attaching the file:

Date-Wise-Record.xlsx

Dear Saqib,
Thanks for the reply,first issue date problem is solved. i attached new sheet for desired result.
cell L2 =SUMIFS($E:$E,$A:$A,"<="&$G2,$A:$A,">="&DATE(YEAR($G2),MONTH($G2),1),$B:$B,LEFT(I$1,3)&"*") for sum item qty MD result is not OK so i mention it by manually in cell F2 because MD + BI total with equal to Total Qty of july-2017 cell H2.
rest of column i entered value i required. if you have query of it please ask me.
Thanks

i am using to calculate count value if Item by this formula,
=COUNTIFS($C:$C,LEFT(J$1,2),$A:$A,"<="&$H2,$A:$A,">="&DATE(YEAR($H2),MONTH($H2),1)), with additional column so i want to apply it direct on column B then this formula is not working. why?
=COUNTIFS($B:$B,LEFT(J$1,2),$A:$A,"<="&$H2,$A:$A,">="&DATE(YEAR($H2),MONTH($H2),1))
can anybody help me for this.
snap is attached for you.
Thanks.

Your Condition says this:-
"If the content in B Column is Equal to Left 2 Charters of J2, Count it."
But Content in B Column is not just two characters of J2, it is 2 Characters of J2 followed by some other characters.

You need to feed that in the formulae. Note in the formula I mentioned I used left formulae with "& "*"" that means anything can follow these two characters. Got it?

Thanks Saqib Qureshi,
Thank a lot to explained me.