Sumproduct

[elemexcel]

Hello

I have a spreadsheet full of orders for Jan-18 to Sep-18. I am trying to extract data from it - I have managed to extract the most popular product per month using this formula -

{=IFERROR(INDEX('Sheet1'!$N$2:$N$10026,MODE(IF(MONTH('Sheet1'!$AB$2:$AB$10026)=(MONTH(Sheet2!BZ5))*(YEAR('Sheet1'!$AB$2:$AB$10026)=(YEAR(Sheet2!BZ5))),MATCH('Sheet1'!$N$2:$N$10026,'Sheet1'!$N$2:$N$10026,0)))),"")}

However, when I want to try and extract how many of each of the most popular products were sold every month, I can't seem to get it.

I have tried using SUMIF/S // COUNTIF/S but to no avail so tried using SUMPRODUCT and can't get it to work - can somebody point me in the right direction pls?

=SUMPRODUCT(--((MONTH('Sheet1'!$AB$2:$AB$10026)=(MONTH(Sheet2!BZ5))*(YEAR('Sheet1'!$AB$2:$AB$10026)=(YEAR(Sheet2!BZ5)),‘Sheet1'!$N$2:$N$1048576)=(Sheet2!$CA5))

Thanks so much

 

[arthurbr]

SUMPRODUCT needs identical ranges. =SUMPRODUCT(--(MONTH('Sheet1'!$AB$2:$AB$10026)=MONTH(Sheet2!BZ5)*YEAR('Sheet1'!$AB$2:$AB$10026)=YEAR(Sheet2!BZ5),‘Sheet1'!$N$2:$ N$10026=Sheet2!$CA5))
(Check the parenthesis I'm not quite sure)