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

Question

How use SUMIF with month & year with text criteria,

Exp!

I want to sum

A                  B                C
Date              Item Code        QTY

01-12-16          86000             50
15-12-16          86021             20
01-02-17          86022             100
01-03-17          86023             50       

Now i want sum result of only Dec-16 of 86000 on an external sheet.

Answer 1

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