I need COUNTIFS or SUMPRODUCT formula to count ONLY Non-Consecutive values

[justinbelkin]

I've attached both a screenshot image, and a sample file to this post. I'm trying to count the number of instances where the values in Column F ("Position") fall within the range indicated in Column A ("Position Size Range") BUT to not double-count values, which are defined as those values that appear consecutively in Column F.

So, for example, my total count for Column F should equal 186, but instead it counts 472, because it's counting all occurences and not just the non-consecutive occurences. I've tried array formulas from Domenic posted on other boards, but that doesn't seem to exactly meet my needs.

Ideally, I'd prefer not use an array formula, too, unless that's the only way to achieve my goal. I am not looking for a VBA solution either.

CountIfs Example.jpg

[daffodil11]

This works if you remove the spaces between the positions. (This can be done quickly by selecting F3:F5000, choose Find & Select -> Special, choose Blanks. Hit Okay, then click the Delete button on Cells menu.)

Plop this into E3. =SUMPRODUCT((1/COUNTIF($F$3:$F$474,$F$3:$F$474)*($F$3:$F$474>=1)*($F$3:$F$474<=2500)))

There are actually 197 unique values.

[justinbelkin]

Thank you daffodil11. That did work perfectly. It would be nice to be able to not have to delete the blanks, however, I suppose that would require an array formula?

[daffodil11]

Indeed. I will think upon it.

Problems are just learning opportunities. There's always another workaround.

[daffodil11]

If you're not averse to using a helper column and an array, a formula could be utilized to filter the unique values values automatically in ascending order in a separate column.

Then with the application of INDIRECT to create a dynamic range, you wouldn't have to worry about manipulating your data source.

MAX would identify the row with the last consecutive nonblank cell, MATCH would pull back that row #, and INDIRECT would let you create ranges for the formula from G3 to GXXX and the final version would look something like:

Please Login or Register  to view this content.