Team,
I am trying to find a solution to below problem ... ..
I have set of Customer Data, which carry duplicate project ID's based on month selection.
When i am trying to extract unique count based above 3 selection, i am getting incorrect counts. Please advise where i am doing wrong.
Sample file attached with formula which i was trying to use.
Cheers
hi there. don't think such unique count for multiple conditional problem can be done via SUMPRODUCT. try this array formula:
=SUM(IF(FREQUENCY(IF($B$3:$B$351=$H3,IF($C$3:$C$351=I$2,$D$3:$D$351)),$D$3:$D$351)>0,1))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
In I3:
=SUM(--(FREQUENCY(IF($B$3:$B$351=$H3,IF($C$3:$C$351=I$2,MATCH($D$3:$D$351,$D$3:$D$351,0))),ROW($D$3:$D$351)-ROW(D3)+1)>0))
... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Once confirmed, drag copy across.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
or you can use PivotTable with DataModel (Ex2010 Pro Plus will need PowerPivot install to use DataModel)
ppdm.jpg
datamodel.jpg
Thank you benishiryo ... this is helpful, however some reason i was very confident on Sumproduct to achieve this :-) .. thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks