CODEWITHSUNDEEP
excelcountifsumifs
Spencer Monckton
Spencer Monckton

Reputation: 3

Count unique values in column A based on a moving range of criteria in column B

Okay, so this is my first question, let's hope I can explain it well...

Essentially, I would like to count the number of unique values in column A, but from a subset of those which have, in column B, a value that falls within a specified range.

Here's an example:

ColumnA     ColumnB
potato      29.1
potato      29.7
potato      30.3
potato      31.0
bean        31.6
apple       32.2
apple       32.8
bean        33.5
bean        34.0
apple       34.3
potato      35.0

Count b/w 29-31:    1               
Count b/w 30-32:    2           
Count b/w 31-33:    3               
Count b/w 32-34:    2           
Count b/w 33-35:    3

In other words, I want to know how many unique items are present within each range (as specified by column B), and I want to carry that down through a series of overlapping ranges.

So far, the best I've been able to come up with is a COUNTIFS formula that counts the total number of records in each range. e.g.:

=COUNTIFS(B1:B11,">=29",B1:B11,"<=31")

=COUNTIFS(B1:B11,">=30",B1:B11,"<=32")

=COUNTIFS(B1:B11,">=31",B1:B11,"<=33")

etc...

And this obviously doesn't even reference column A. I've tried a few different array formulas based on similar questions, but they're always solving a slightly different problem, so I've been largely unsuccessful.

Any help much appreciated! Thank you.

Upvotes: 0

Views: 369

Answers (1)

Scott Craner
Scott Craner

Reputation: 152585

You would use this array formula:

=SUM(IF(($B$2:$B$12>=A16)*($B$2:$B$12<=B16),(1/COUNTIFS($A$2:$A$12,$A$2:$A$12,$B$2:$B$12,">=" & A16,$B$2:$B$12,"<=" & B16))))

Being and array formula it must be confirmed with Ctrl-Shift-Enter when exiting edit mode. If done correctly then Excel will put the {} around the formula automatically.

enter image description here

It finds all the rows where the data in B is between the extents then uses the 1/COUNTIF() to find the unique values.

Upvotes: 1

Related Questions