LinkBack URL
About LinkBacks
Hi
I want to do a count of unique occurrences of data in one column based on criteria in another column.
I have attached a sample worksheet with the data. I want to get the result in cell I26.
Thanks.
ltsolis
Hi
I want to do a count of unique occurrences of data in one column based on criteria in another column.
I have attached a sample worksheet with the data. I want to get the result in cell I26.
Thanks.
ltsolis
Last edited by ltsolis; 09-18-2011 at 10:45 PM.
Try:
confirmed with CTRL+SHIFT+ENTER not just ENTER
=COUNT(1/FREQUENCY(IF(RFPDataTable[Join Month]=3,IF(RFPDataTable[ID]<>"",MATCH(RFPDataTable[ID],RFPDataTable[ID],0))),ROW(RFPDataTable[ID])-MIN(ROW(RFPDataTable[ID]))+1))
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
This array formula in I26:
=COUNT(1/FREQUENCY(IF(I2:I18<>"",IF(MONTH(B2:B18)=3,MATCH(I2:I18,I2:I18,0))),ROW(I2:I18)-ROW(I2)+1))
...confirmed with CTRL-SHIFT-ENTER to activate the array. Result should be 5.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Wow...both formulas worked perfectly, and looks just about the same. Thank you very much.
I have another puzzle that I could use your assistance on. It's related to the unique counting, but adds a twist.
I have added a new column with some numbers to the table. I want to sum (in J28) the numbers but only the single instance of the number as determined by the unique occurrence of the ID. Each ID has a number associated with it, and is repeated the same amount of times as the ID, but I want to sum only one of each instance.
Your help would be greatly appreciated.
Please let me know if I need to do a new thread.
ltsolis
If you are still including the criteria of Month=3, try:
else if you are only looking for unique ID/Number combinations try:=SUM(IF(FREQUENCY(IF(RFPDataTable[Join Month]=3,IF(RFPDataTable[ID]<>"",MATCH(RFPDataTable[ID]&RFPDataTable[Number],RFPDataTable[ID]&RFPDataTable[Number],0))),ROW(RFPDataTable[ID])-MIN(ROW(RFPDataTable[ID]))+1),RFPDataTable[Number]))
either is CSE confirmed.=SUM(IF(FREQUENCY(IF(RFPDataTable[ID]<>"",MATCH(RFPDataTable[ID]&RFPDataTable[Number],RFPDataTable[ID]&RFPDataTable[Number],0)),ROW(RFPDataTable[ID])-MIN(ROW(RFPDataTable[ID]))+1),RFPDataTable[Number]))
Works perfectly...thank you very much.
ltsolis
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks