Use SUMPRODUCT, LEFT, and YEAR functions together

[excelmagic20]

Hello everyone!

I would like use the SUMPRODUCT, LEFT, and the YEAR functions simultaneously. Column D contains data in the format '2017/01' (yyyy/mm), column E contains the amounts in $ that I would like to add together. Cell C2 contains the date in format 'dd/mm/yyyy'. I would like to have a function that adds the values in E if the year(C2) = left(D:D,4) and if month(C2) = right(D:D, 2)

I wrote this function but unfortunately Excel returns 0: =SUMPRODUCT(--(LEFT(D:D,4)=YEAR(C2)), --(RIGHT(D:D,2)=MONTH(C2)),E:E). If I remove the YEAR/MONTH functions, the formula, which becomes =SUMPRODUCT(--(LEFT(D:D,4)=C2), --(RIGHT(D:D,2)=C2),E:E) works correctly. So, apparently the MONTH/YEAR functions are causing the error.

Do you have any suggestion on how I could make the formula work?

Thanks a lot in advance

[AliGW]

Welcome to the forum.

There are instructions at the top of the page telling you how to attach your sample workbook.

[excelmagic20]

Thanks! And thanks for the suggestion I have now attached an Excel sample file

[rio.south]

Try this

=sumproduct((left(d2:d20,4)=text(year(c2),0))*(right(d2:d20,2)=text(month(c2),"00"))*(e2:e20))

[p45cal]

or:
=SUMPRODUCT(--(D:D=TEXT(C2,"yyyy/mm")),E:E)
but instead of using entire columns (heavy on resources) use the likes of:
=SUMPRODUCT(--(D2:D19=TEXT(C2,"yyyy/mm")),E2:E19)