elemexcel

New Member
Joined
Aug 31, 2018
Messages
7
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
 

Some videos you may like

Excel Facts

Excel Can Read to You
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
Joined
Dec 8, 2006
Messages
2,076
Office Version
  1. 2010
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,753
Messages
5,524,641
Members
409,597
Latest member
Dannydev

This Week's Hot Topics

Top