Hi Guys,
i am trying to count the # of unique items bought by each customer by month.
i have the customer codes in first column , item codes in 2nd column and monthly sales.
i am attaching my excel file. can you please help me figure out what would be the easiest formula here ?? (knowing that the report is very long!)
for example, if you filter to customer # 334, you will find that he bought 25 unique items in Jan 2013 (excluding zero sales). how can i get that result (25) in a formula??? Helpppp
Have a look at the attached for one way to do it.
thanks alot for the help! cheers
No problem. Happy to help.
Or you can also use this
=SUMPRODUCT(($C$5:$C$8138=$Q5)*(--(E$5:E$8138>0)))
Drag formula across and down.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Hi guys,
i've reviewing the formulas you sent me but they're not excluding duplicate item codes value.
i am attaching a single customer code #622 that has bought duplicate items in Jan 2013. i want the result to show me only the number of UNIQUE items bought. if i use your formula, the result would be 136 items (which is taking into consideration the item repeated more than once) the result i need for unique items bought should be 91 unique items any views on how to do the formula???
the new example of customer # 622 is in "GR NEW" file.
hello?? helpneed to submit the report tomorrow...
Would this be what you are looking for?
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Hi,
a pivot table could be a great help
In R2
=SUM(IF(ISNUMBER(1/FREQUENCY(IF($C$5:$C$8138=Q5,IF($E$5:$E$8138<>0,MATCH($D$5:$D$8138,$D$5:$D$8138;0))),ROW($A$5:$A$8138)-4)),1))
array formula to be confirmed with control+shift+enter
Regards
-----------------------------------------------------
At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.
Please, mark your thread [SOLVED] if you received your answer.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Thanks alot CANAPONE! finally this is what i needed.
can i ask you on another note why did u use ROW A although it's empty...?
ROW($A$5:$A$8138) creates a range of numbers from 5 to 8138
5,6,7.......8136,8137,8138
but the formula needs 1 to 8134 as thats how many rows between $E$5:$E$8138
so subtract 4
ROW($A$5:$A$8138)-4 = 1,2,3,4,.....8132,8133,8134
canopone could have used row(a1:a8134) instead or even row(z1:z8134) its all the same
Last edited by martindwilson; 11-03-2013 at 01:22 PM.
thanks Martin, very helpful...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks