Counting combinations and ordering them
Hi!!
I need help on counting how many times a certain combination occurs on a table, and at each occurance state if its the first, second or xth time it has already shown up.
I can get how many times the combination repeats with a COUNTIFS, but I need to now at each ocurrance whether its the first time the combination appears or what number it is.
ExampleCountingOccurances.xlsx
Thanks a lot!!! :D
Use in E1 and copy down
=COUNTIFS(B$2:B2,B2,C$2:C2,C2)
Life's a spreadsheet, Excel!
Say thanks, Click *
Awesome!!! Thanks so much. Is there a way to integrate the two into just one formula. The D column is not going to be displayed, and I would rather not get messed up with hiding columns, as the actual table is pretty extensive. Cheers, BTW, congrats on your award for star of the month... clearly well deserved.Originally Posted by Ace_XL
You mean display results of Col D & Col E perhaps separated by a "/"? Use..Is there a way to integrate the two into just one formula.
=COUNTIFS(B$2:B$1048576,B2,C$2:C$1048576,C2)&" / "&COUNTIFS(B$2:B2,B2,C$2:C2,C2)
Just delete it. It is not a source Column for other calculationsThe D column is not going to be displayed, and I would rather not get messed up with hiding columns, as the actual table is pretty extensive
ThanksBTW, congrats on your award for star of the month... clearly well deserved.
I encountered a bit of a problem. Since I already have some data and the information is fixed. I need the "counter" of occurrences to add up from the last number showing in the occurrence. The max number of occurrences is 8, then they will no longer repeat, but until all the current data I have goes through the cycle, the formula above is not enough.
Cheers
ExampleOccurances.xlsx
Try from Cell C9 onwards..
=SUM(COUNTIFS(A$9:A9,A9,B$9:B9,B9),SUMIFS($C$2:$C$8,$A$2:$A$8,A9,$B$2:$B$8,B9)) and copy down
Like a Charm!!!you are awesome!!
What part of the equation is the one that calls to check the last occurrence? I want to use it so I can display the date for the previous one in another column, and then do a datedif to check the days between reps.
Cheers
I sure would like too buy you a beer jejeje
The first part of the SUM i.e. COUNTIFS(A$9:A9,A9,B$9:B9,B9) is the similar to the one in Post #2 and applies to the lower range of (presumbaly new!) data
The second part of the SUM i.e. SUMIFS($C$2:$C$8,$A$2:$A$8,A9,$B$2:$B$8,B9) checks for the 'counter' of earlier instances in the upper range
The SUM just adds them together to give you the final result
Ok, great. So i came up with this formula:
Formula:
Please Login or Register to view this content.
but I'm missing the correct reference in the sum where U129 is, it should be the T$value for previous occurrence of D:E. I think...
Hi,
Actually, I came up with another approach. And I'm almost there, except it counts all the previous ocurrences, and not just the last one. Here is the work sheet...
ExampleOccurances.xlsx
Thanks a million!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks