Counting unique values in a row

  • Thread starter Thread starter BASCRUMMY
  • Start date Start date
B

BASCRUMMY

I am attempting to count the number of times a specific value range occurs in
a row throughout a large spreadsheet. For example, I have three values in
multipe rows:
A1=10, B1=20, C1=30,
A2=20, B2=10, C2=30.
A3=50, B3=50, C3=50
I want to create a formula that will allow me to count the number of rows
that contain 50,50,50 within a large area.
 
Here's an array formula that will help you count unique instances

SUM(1/COUNTIF(data_range,data_range))

CTRL+SHIFT+ENTER to activate the array
 
Here's another one:

=SUMPRODUCT(--(MMULT(--(A1:C100=50),{1;1;1})=3))

Note that using this the range is limited to no more that 5460 rows
 
I think this is going to work, but I think I am caught up on what to put in
for lastrow. Also should I put my whole data range in where A1 is?
 
I am caught up on what to put in for lastrow.

"lastrow" should be the count of total rows in your range. If your range of
data was from A1:C10 then lastrow = 10. However, you'd need to change the
syntax from:

ROW(INDIRECT("1:"&lastrow))

To:

ROW(INDIRECT("1:10"))

Probably easier if you replace lastrow with ROWS(rng), where rng is your
actual range. Like this:

ROW(INDIRECT("1:"&ROWS(A1:C10)))
Also should I put my whole data range in where A1 is?
Click to expand...

No. Use whatever is the top left cell in your range. If your data is in the
range K19:M27 the top left cell in the range is K19 then you'd use K19.

This is why you should always tell use where your real data is located. It
seems most people use "fake" ranges when they post a question and in some
circumstances the location of the data can matter in how a formula is
crafted for the best result.
 
Sometimes we overlook the easiest solution!

=SUMPRODUCT(--(A1:A100=50),--(B1:B100=50),--(C1:C100=50))
 
If you have a minute -
Can you give a quick explanation of how this works? What does the " -- "
mean in a formula? I don't remember seeing it.
thanks for your time
dp
 
Back
Top