# Sumproduct

#### elemexcel

##### New Member
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

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### arthurbr

##### Well-known Member
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)

Replies
0
Views
241
Replies
3
Views
63
Replies
6
Views
34
Replies
4
Views
119
Replies
1
Views
34