Hi Guru's
Stumped on another one and wondering if I can get some expert help please?
I need a formula to count how many times a company has a vehicle, that has a Permit A and a Permit B.
Difficult part for me is that vehicle REGO is listed many times.
Manual answers are:
Joe Trump Co 2
Don Biden 1
Last edited by tbomdiggity; 07-19-2024 at 01:09 AM. Reason: solved
Not sure how you arrive at 2?
You have 7 with Joe and A
You have 5 with Joe and B
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
I understand what you need, but are you still using Excel 2016 or something newer?
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. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
G2=IF($F2<>"",SUM(IF(FREQUENCY(IF($C$2:$C$19=F2,IF(ISNUMBER(MATCH($B$2:$B$19,$B$9:$B$10,0)),MATCH($D$2:$D$19,$D$2:$D$19,0))),ROW($A$2:$A$19)-ROW($A$2)+1),1)),"")
Copy down
why ? 2 and 1 ?
If you have upgraded to 365, this should do what you want:
Please Login or Register to view this content.
Im using Office 365
Thanks for your responses and Im sorry, perhaps I didnt explain well enough.
Joe Trump Co has 2 vehicles REGO's that have both an A and B Permit.
(light green match, dark green match)
Don Biden has 1 vehicle that has a A and B Permit
(pink)
Please update your forum profile and see the 365 solution in post #5.
Thank you AliGW!
Glad to have helped.
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.
Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
Try in G2, copy down:
=LET(x,CHOOSECOLS(UNIQUE(FILTER($B$2:$D$20,($C$2:$C$20=F2)*(($B$2:$B$20="A")+($B$2:$B$20="B")))),3),COUNTA(x)-COUNTA(UNIQUE(x)))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks