COUNTIF

0

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?

Answer
Discuss

Discussion

Perhaps, with a sample worksheet, I might understand the question. :-)
Variatus (rep: 3368) Jul 16, '19 at 9:25 pm
I've attached a sample workbook. I thought you might need that : )

Column J = Shows duplicates (Policy number - Column G) that match (Work type - Column B)

Column K = # of occurances policy numbers from column G also match work type for column B

As you can see, I have a formula that shows the # of occurances but not # of occurances specific to work type in column B. That's the main thing I'm trying to figure out. 

But also to show only the max number of occurances (if possible)
Sroncey21 (rep: 62) Jul 16, '19 at 9:35 pm
Add to Discussion

Answers

0
Selected Answer

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)-1

would 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.

Discuss
0

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.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login