• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Error getting in SUMPRODUCT formula

A

Ajinkya

Member
Hi,

Im trying to get details from another sheet into report sheet with the help of following formula with the criteria

but getting error.

=SUMPRODUCT((SummarySalesData!$C$2:$I$11=B8)*(SummarySalesData!$D$2:$I$11=C8)*(SummarySalesData!E$1:H$1=D7)*(SummarySalesData!$B$2:$I$11=$C$4)*(SummarySalesData!$E$2:$PQ$1615))


Pls help


sample file is uploaded on….

https://hotfile.com/dl/163863743/86feab5/June_2012_Sales_Data_Growth_V1.xlsx.html
 
This is very unbelievable moment for me, thanks god.


my blog has been going through "The Great Key Master, Chandoo Sir"


well sir, the range is re-selected again as per....


=SUMPRODUCT((SummarySalesData!$C$2:$C$11=B8)*(SummarySalesData!$D$2:$D$11=C8)*(SummarySalesData!E$1:H$1=D7)*(SummarySalesData!$B$2:$B$11=$C$4)*(SummarySalesData!$B$2:$I$11))


but getting #VALUE! error.
 
Hi Ajinkya,


If your required output is 1508, then you can do it by creating a key value by merging the lookup values and then run a simple vlookup keeping the kye as the lookup value. The formula is here below:


=VLOOKUP(C4&B8&C8&D7,SummarySalesData!A2:I11,9,0)


I mailed you the workbook


Regards,

Kaushik
 
In my learning days, I had been never seen /uses of vlookup formula


which Koushik has given.


Thanks Koushik for your awesome logic


I got the exact result.


Thanks once again.
 
As Chandoo pointed out, your array sizes are not the same. You big block of data is 10x8, and most of the other arrays are 10x1 (which is okay), but your E1:H1 array is only a 1x4, and that will just not work. It needs to be either a 1x8 or a 10x1 size array.


Note, I use notation Row x Column, as that is how XL lists it when you select a range.


EDIT: Too late, I see that kaushik03 has solved it!
 
Ajinkya,


Since you are on Excel 2007 or later, you could also use SUMIFS without helper column.


In Report!D8, then copy down & across each sections.


Code: 
=SUMIFS(INDEX(SummarySalesData!$E:$H,0,MATCH(D$7,SummarySalesData!$E$1:$H$1,0)),SummarySalesData!$B:$B,$C$4,SummarySalesData!$D:$D,$C8,SummarySalesData!$C:$C,LOOKUP("zzzz",$B$8:$B8))


To avoid zero display, use custom cell format: #,##0;-#,##0;;
 
You must log in or register to reply here.
Back
Top