How use SUMIF with month & year with text criteria on external sheet

Assuming your data is as per the image below then enter the following formula in Cell G2

=SUMPRODUCT((MONTH($A$2:$A$5)=12)*(YEAR($A$2:$A$5)=2016)*($B$2:$B$5=86000)*(C2:C5))

or

=SUMPRODUCT((MONTH($A$2:$A$5)=F2)*(YEAR($A$2:$A$5)=F3)*($B$2:$B$5=F4)*(C2:C5))

Here, SUMIFS may not be useful instead you can use SUM as

=SUM(IF(MONTH($A$2:$A$5)=F2,IF(YEAR($A$2:$A$5)=F3,IF($B$2:$B$5=F4,$C$2:$C$5))))

This is an array formula so commit it by pressing Ctrl+Shift+Enter.

EDIT :

If you have to use month name instead of number i.e. if your are using Dec instead of 12 then use following formula

=SUMPRODUCT((MONTH($A$2:$A$5)=MONTH(F2&"1"))*(YEAR($A$2:$A$5)=F3)*($B$2:$B$5=F4)*(C2:C5))

Finally i got the solution.

  A                 B                C  

1.......... Date .......... Item Code.......QTY
2
3.......... 01-12-16 .......... 86000.......... 50
4.......... 15-12-16 .......... 86021.......... 20
5.......... 01-02-17 .......... 86022.......... 100
6.......... 01-03-17 .......... 86023.......... 50

Where i want sum of 86000 of Only Dec-2016, i put this formula in the cell & my problem solved

=SUMPRODUCT((MONTH($A$3:$A$6)=12)(YEAR($A$3:$A$6)=2016)($C$3:$C$6)*($B$3:$B$6=86000))

Use Table range, that helps you addressing in each sheets of entire workbook.

Assume date field data type is text:

=SUMIFS(Table1[QTY], Table1[Date], "01-12-16", Table1[Item Code], "86000")

If Date end with 12-16:

=SUMIFS(Table1[QTY], RIGHT(Table1[Date], 5) , "12-16", Table1[Item Code], "86000")

You can use SUMPRODUCT:

From the screenshot above, I added two extra rows of data to show you how this works. What I did is to convert all the dates to the first date of the month. Here is the formula assuming you have the result on cell E2.

=SUMPRODUCT($C$2:$C$7,--(DATE(YEAR($A$2:$A$7),MONTH($A$2:$A$7),1)=DATE(2016,12,1)),--($B$2:$B$7=86000))

You can also move the criteria into another table so it is easier for you to change them in the future.

You can also use SUMIFS

=SUMIFS($C$2:$C$5,$A$2:$A$5,">="&F2,$A$2:$A$5,"<="&EOMONTH(F2,0),$B$2:$B$5,G2)

The date can be entered as text or as an actual date.