Search code examples
excelexcel-formulasumsumifs

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

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.

Solution

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