conditional count of single occurences

  • Thread starter Thread starter icystorm
  • Start date Start date
I

icystorm

There are two bouncing balls. Let's call them "red" and "blue". I want
to count the number of times each ball bounces higher than one inch
when dropped.

A1, B1
ball, height of bounce (inches)
red, 2.30
red, 1.20
red, 0.90
red, 0.25
blue, 2.35
blue, 1.22
blue, 0.91
blue, 0.23

For the red ball bounces that meets the bounce height criteria...

{=SUMPRODUCT($A$2:$A$9="red")*($B$2:$B$9>1)}

....returns 2.

Likewise for the blue ball.

---

Next, regardless of how many times the red ball bounces higher than
one inch, if it happens at least once, I only want to count it as a
single event, rather than multiple events. Likewise for the blue ball.

{=IF(SUMPRODUCT(($A$2:$A$9="red")*($B$2:$B$5>1))>0,1,0)}

....returns 1, as desired, for the red ball. Likewise for the blue
ball.

---

Okay, now the complicated part...

I want to place the results of the red ball and blue ball single
events in a single cell.

The result should be two in my example, because the red ball bounced
higher than one inch twice, as did the blue ball, so each instance
counts as a single event per ball.

Any recommendations?
 
I want to place the results of the red ball and blue ball single
events in a single cell.

The result should be two in my example, because the red ball bounced
higher than one inch twice, as did the blue ball, so each instance
counts as a single event per ball.
Click to expand...

=(SUMPRODUCT(($A$2:$A$9="red")*($B$2:$B$5>1))>0)
+(SUMPRODUCT(($A$2:$A$9="blue")*($B$2:$B$5>1))>0)

PS: For broader participation, you might want to post future
inquiries using the MS Answers Forums at
http://social.answers.microsoft.com/Forums/en-US/category/officeexcel.
It's not that I like that forum. It's just that MS has ceased to
support the Usenet newsgroups. Hence, participation here is limited
to the sites that share a common newsgroup mirror, which is no longer
centralized at MS.
 
=(SUMPRODUCT(($A$2:$A$9="red")*($B$2:$B$5>1))>0)
+(SUMPRODUCT(($A$2:$A$9="blue")*($B$2:$B$5>1))>0)

PS:  For broader participation, you might want to post future
inquiries using the MS Answers Forums athttp://social.answers.microsoft.com/Forums/en-US/category/officeexcel.
It's not that I like that forum.  It's just that MS has ceased to
support the Usenet newsgroups.  Hence, participation here is limited
to the sites that share a common newsgroup mirror, which is no longer
centralized at MS.
Click to expand...

Thank you, Joeu.

I made my hypothetical case exceedingly easy. I reality, I am dealing
with thousands of different "colored balls", so it is impractical to
add them all.

Instead, what I need is a way to substitute some type of incrementing
variable for $A$2:$A$9="red" and $A$2:$A$9="blue". In fact, I could
easily assign incrementing numbers as an index. For example, all red
cases would be 1, all blue cases 2, and so forth. Could that possibly
help in creating the formula I am seeking?

My original idea to use sumproduct may not be the right approach.

Again, the single cell must return a solution based on this idea... If
any blue ball bounces higher than 1 inch at least once, return 1,
PLUS, if any red ball bounces higher than 1 inch at least once, return
1, PLUS, if any green ball bounces higher than 1 inch at least once,
return 1... and so forth. So, if all three cases were true, the final
return in the cell would be 3. But, I need to be able to do that for
thousands of different "colored balls", so summing them individually
is not practical.

Thanks also for your advice on the MS Answers Forum.

Cheers,
Joseph
 
Thank you, Joeu.

I made my hypothetical case exceedingly easy. I reality, I am dealing
with thousands of different "colored balls", so it is impractical to
add them all.

Instead, what I need is a way to substitute some type of incrementing
variable for $A$2:$A$9="red" and $A$2:$A$9="blue". In fact, I could
easily assign incrementing numbers as an index. For example, all red
cases would be 1, all blue cases 2, and so forth. Could that possibly
help in creating the formula I am seeking?

My original idea to use sumproduct may not be the right approach.

Again, the single cell must return a solution based on this idea... If
any blue ball bounces higher than 1 inch at least once, return 1,
PLUS, if any red ball bounces higher than 1 inch at least once, return
1, PLUS, if any green ball bounces higher than 1 inch at least once,
return 1... and so forth. So, if all three cases were true, the final
return in the cell would be 3. But, I need to be able to do that for
thousands of different "colored balls", so summing them individually
is not practical.

Thanks also for your advice on the MS Answers Forum.

Cheers,
Joseph- Hide quoted text -

- Show quoted text -
Click to expand...
As answered in the answered forum

Sub CountBlocksGreterThanONE_SAS()
lr = Cells(Rows.Count, 1).End(xlUp).Row
Set mr = Range("A1:a" & lr)
mr.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
For Each c In mr.SpecialCells(xlCellTypeVisible)
mf = Evaluate("=SUMPRODUCT(($A$2:$A$" & lr & "=""" _
& c & """)*($B$2:$B$" & lr & ">1))")
If mf > 0 Then ms = ms & c & " "
Next c
ActiveSheet.ShowAllData
MsgBox "Colors greater than 1 are: " & ms
End Sub
Sub CountBlocksGreterThanONEARRAY_SAS()
ma = Array("red", "green")
lr = Cells(Rows.Count, 1).End(xlUp).Row
Set mr = Range("A1:a" & lr)
For Each c In ma
' MsgBox c
mf = Evaluate("=SUMPRODUCT(($A$2:$A$" & lr & "=""" _
& c & """)*($B$2:$B$" & lr & ">1))")
'MsgBox mf
If mf > 0 Then ms = ms & c & " "
Next c
MsgBox "Colors greater than 1 are: " & ms
End Sub
 
There are two bouncing balls. Let's call them "red" and "blue". I want
to count the number of times each ball bounces higher than one inch
when dropped.

A1, B1
ball, height of bounce (inches)
red, 2.30
red, 1.20
red, 0.90
red, 0.25
blue, 2.35
blue, 1.22
blue, 0.91
blue, 0.23

For the red ball bounces that meets the bounce height criteria...

{=SUMPRODUCT($A$2:$A$9="red")*($B$2:$B$9>1)}

...returns 2.

Likewise for the blue ball.
Click to expand...

I'm running Excel 2010, and that can be done as
=sumproduct(--($a$2:$a$9="red"),--($b$2:$b$9=">1")).
 
Back
Top