0

Count by Sum

So I have a report I'm working on.  I want a clean count of number of orders (External Document No.) that have X number of products types in an order. 

So to simplify it, I have orders:

Order 1: Product A, Product B, Product C
Order 2: Product A X 3, Product B
Order 3: Product B x 6

And my  goal is to say how many orders have:

2-4 products A & B in them – Answer should return 2 (order 1 & 2)
5-8 products A & B in them – Answer should return 1 (Order 3)
Etc.

Poking around with all of this it seems like I need to do a sort by sum type of thing, but the following is not working how I expected it to.  I believe my understanding of the sort by Sum is what's giving me the issue.  The """Item No.""" reference in the sum formula doesn't really make sense to me, I thought it should be a filter on External Document Number of some sort since that's the count I'm looking at.

=NL("CountUnique","Item Ledger Entry","External Document No.","Entry Type","Sale","Source No.","RETAIL","Location Code",E$4,"Dept Code",E$5,"Item No.",$C$54,"Posting Date",$B$2,"Sales Amount (Actual)","<>0","=NL(""Sum"",""Item Ledger Entry"",""Quantity"",""Entry Type"",""Sale"",""Sales Amount (Actual)"",""<>0"",""Item No."",NF(,""Item No.""))",$D56)

 

C54 is referencing  this filter =NL("Filter","Item","No.","Parent Item Category",$C$53) 
D56 has the range of products I'm looking to sum 1..5

 

Any advice would be greatly appreciated!

0 comments

Please sign in to leave a comment.