Road Bike, Cycling Forums banner

1 - 20 of 30 Posts

·
No team-cest unless 8+!
Joined
·
7,158 Posts
Discussion Starter · #1 ·
Alright...

Column A= categorical variable... there are about 38 different categories down maybe... 11,000 rows...

Column B= bool... (1 or blank based on whether or not column A is in the top X percentile... it is an active formula)

I need to count uniques in column A with a 1 in column B outputted to a single cell.

TIA!
 

·
Moderatus Puisne
Joined
·
15,883 Posts
Have fun with visual basic!

Cruzer2424 said:
Alright...

Column A= categorical variable... there are about 38 different categories down maybe... 11,000 rows...

Column B= bool... (1 or blank based on whether or not column A is in the top X percentile... it is an active formula)

I need to count uniques in column A with a 1 in column B outputted to a single cell.

TIA!
 

·
No team-cest unless 8+!
Joined
·
7,158 Posts
Discussion Starter · #4 ·
Creakyknees said:
Wait, I think you're overthinking this. why not just sort and subtotal on A?
It's in a sheet I can't edit. Well... not edit, but it's a data sheet that gets overwritten often. The "summary" sheet needs to get written so that nothing needs to happen in sheet A every time its overwritten
 

·
No team-cest unless 8+!
Joined
·
7,158 Posts
Discussion Starter · #6 ·
q_and_a said:
use countif
How do I tell it to only count uniques?

//edit: I was thinking that originally, but I'm not sure how to use the frequency command well enough to make it work.
 

·
hit it
Joined
·
4,006 Posts
Pivot table.....

"Row" is categorical variable on top, bool on bottom

"Data" is Count of Bool

Then use your filter.
 

·
No hero that's understood
Joined
·
6,100 Posts
Cruzer2424 said:
How do I tell it to only count uniques?

//edit: I was thinking that originally, but I'm not sure how to use the frequency command well enough to make it work.
I'd actually dump the data in access and then the answer is easier using find dups. I'll have to think about it.
 

·
No hero that's understood
Joined
·
6,100 Posts
Actually, the more I think about it, I'd filter out the b's and filter out the 0's and then count the filtered data.
 

·
No team-cest unless 8+!
Joined
·
7,158 Posts
Discussion Starter · #10 ·
q_and_a said:
I'd actually dump the data in access and then the answer is easier using find dups. I'll have to think about it.
Bleh. I can find the numbers for this specific dataset, but I need the answer via functions so it can be found for future datasets with no added work, which I don't know how to do. :(

I'm fairly certain its going to be a combination of SUM, FREQUENCY and maybe an IF function...
 

·
Registered
Joined
·
4,572 Posts
This thread is so hot....
 

·
Windrider (Stubborn)
Joined
·
22,021 Posts
NUTT said:
Pivot table.....

"Row" is categorical variable on top, bool on bottom

"Data" is Count of Bool

Then use your filter.
Bingo...than just refresh the pivot table when you have new data.............just include one etra row in the data range.
 

·
Shirtcocker
Joined
·
60,639 Posts
<img src=https://www.untoldentertainment.com/blog/img/2010_03_14/mathIsHard.jpg>
 

·
Could I be a Fred?
Joined
·
7 Posts
Counif...

My first post!!

I would be simple about it and use countif:

=countif(range,1)

and that will count all the cells that have a value of 1 in it. Try it out. Typoe 4 rows of data, 1, 0, 1, 1 and use that function and it will return 2.

-Holly

Edit: need to preview my posts first. Type ... and it will return a 3.....
 

·
Seat's not level
Joined
·
19,371 Posts
Pivot table

Put the "B" in the page section
PUt "A" in the rows.
Display the Count in the field.

Select "1" in the page.

Only
 

·
Seat's not level
Joined
·
19,371 Posts
Pivot table


Put the "B" in the page section
PUt "A" in the rows.
Display the Count in the field.


Select "1" in the page.
Only the unique records with a 1 will show in the table.
The field will tell you how many times it shows up.

 
1 - 20 of 30 Posts
Top