cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Sciguy_1
Level III

Counting rows based on multi condition match

I want to count rows in my dataset if 3 columns (A-B-C) match. I want to enter the value in a new column (D). Here is an example. Thank you for your assistance!

Column AColumn BColumn CColumn D (Counted Tally)
7ZS4
7ZS4
1AP

3

1AP3
1AP3
7ZS4
5EM2
5EM2
7ZS4

  

14 REPLIES 14
Sciguy_1
Level III

Re: Counting rows based on multi condition match

I see I made an error. The header on the last column jar_id equals column J1. Would also like to know the number of times T1 occurs per J1 X SOG X BUCKET combination. Thank you.
julian
Community Manager Community Manager

Re: Counting rows based on multi condition match

Hi @Sciguy_1 ,

 

Here's a table that accomplishes what I think you're after, using the formulas I showed before but adapted for each combination. My calculation for "# times BUCKET appears per: SOG (Correct Answer)" does not match the one you said you manually created, but I am pretty sure what I've done here is correct since it squares with my own manual counting. Can you check and confirm I am not missing something though? 

 

You'll notice that you need a pair of columns for each of these counts, one that counts incrementally in each grouping, and another that finds the total. I've hidden the incremental count columns in the table, but note that you must have these. You can generalize this to really any number of combinations, you're just adjusting the formulas as shown below to include the right columns as your by groups:

 

Screen Shot 2019-06-26 at 5.20.49 PM.png

 

Screen Shot 2019-06-26 at 5.20.54 PM.png

 

Hope this gets your further along,

@julian 

ms
Super User (Alumni) ms
Super User (Alumni)

Re: Counting rows based on multi condition match

An alternative to using two columns to arrive at the counts would be to count the rows of each combination with the ColNumber() function.

 

For example:

Col Number(Row(), :J1, :SOG, :BUCKET);

 

 

 

Sciguy_1
Level III

Re: Counting rows based on multi condition match

I am still having issues getting this to work. The 2 equation version Julian suggested- the first equation is giving me all 1's so not sure what I am doing incorrectly. The single equation method does give me the total number of times a particular bucket appears per SOG. The other part I am still trying to get is the number of times I have to grab a bucket. For example if I grab a bucket and take 4 samples or grab it again and take 19 samples- in each case I only grabbed the bucket once. From my data example EXULU appears a total of 4 times in the 621 SOG. I am wanting to create a tally that would count those 4 times as 1 time. EH8QK appears a total of 19 times in the 621 SOG but I want to count those all as 1 as well. I hope this better explains the second half of the problem. Thank you!

julian
Community Manager Community Manager

Re: Counting rows based on multi condition match

I'm glad the formula @ms posted works for you! I'm curious why mine is failing on your actual table, but perhaps our time is better spent on your other question rather than diagnosing what went wrong with my inferior two-column method! 

 

Can I ask what problem creating that tally is solving? It seems as though you'll just end up with a column of 1s by doing this, since matter the number of occurrences of the pattern you're counting it as 1. I don't think I'm fully grasping what you're wanting to do or what it's helping you accomplish. I think if I knew the end goal I might be able to suggest a simpler overall method than a series of formula columns. 

 

@julian