200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
I have a countif formula here:
=COUNTIF($G$2:G2,G2)
Is there a way to edit this formula to only count the highest "nth" number. Say there is a return of 4 in the count. Is there a way to only make the 4 display and have the count of 1,2, and 3 be blank?
If I understand your requirement correctly, your current formula in column J should do the job.
=COUNTIFS($B:$B,$B2,$G:$G,$G2)Its essential difference from the formula in column K [=COUNTIF($G$2:G2,G2)] is in the definition of the count range. $G$2:G2 counts within a range starting from G2 and ending in the row where the formula resides, excluding anything below that row. $G:$G on the other hand counts everything in the column. The COUNTIFS() function above will return 2 for both, rows 27 and 28.
=COUNTIFS($B:$B,$B2,$G:$G,$G2)-1would return the number of duplicates, meaning it returns 0 if there is only one occurrence. You might then suppress the display of zero in some way so that items which have no duplicates show a blank cell.
One method to suppress the display of zeroes is to set Advanced Options for the worksheet. This will affect all zeroes in the sheet. If that is too broad, perhaps because you need to show zeroes in another column, you can use a cell format like 0;0;; only for the cells in which you want to suppress the display.
I'm not sure about what your goal is. But probably you can work with this:
=IF(COUNTIF($G$2:G2;G2)<COUNTIF([Policy Number];G2);"";COUNTIF($G$2:G2;G2))
Replace the semi colon with a comma (I've got other settings) :)
[Policy Number] = column G of your table. But I would advise to work with real names instead of table names.
Cheers.