Sumproduct counting multiple time

[trone77]

I need the formula below to calculate if this sequence occurs 3 times instead of just once. How could this formula be revised?

=SUMPRODUCT(--($F$342:$F$372=8),--($G$342:$G$372="O"),
--($H$342:$H$372="O"),--($I$342:$I$372="E"),
--($K$342:$K$372="L"),--($L$342:$L$372="M"),--($M$342:$M$372="H"))

 

[Aladin Akyurek]

I need the formula below to calculate if this sequence occurs 3 times instead of just once. How could this formula be revised?

=SUMPRODUCT(--($F$342:$F$372=8),--($G$342:$G$372="O"),
--($H$342:$H$372="O"),--($I$342:$I$372="E"),
--($K$342:$K$372="L"),--($L$342:$L$372="M"),--($M$342:$M$372="H"))

Do you mean to test whether the count this formula calculates is >= 3 ? If so, try:

=SUMPRODUCT(
    --($F$342:$F$372=8),
    --($G$342:$G$372="O"),
    --($H$342:$H$372="O"),
    --($I$342:$I$372="E"),
    --($K$342:$K$372="L"),
    --($L$342:$L$372="M"),
    --($M$342:$M$372="H")) >= 3

 

[AlphaFrog]

This returns TRUE if the count = 3

=SUMPRODUCT(--($F$342:$F$372=8),--($G$342:$G$372="O"),
--($H$342:$H$372="O"),--($I$342:$I$372="E"),
--($K$342:$K$372="L"),--($L$342:$L$372="M"),--($M$342:$M$372="H"))=3

 

[trone77]

Well I was just using 3 as an example, but really just multiple times and if it doesn't occur as many times to just leave it blank and not return false. I needed the formula for a conditional statement.

 

[Aladin Akyurek]

Well I was just using 3 as an example, but really just multiple times and if it doesn't occur as many times to just leave it blank and not return false. I needed the formula for a conditional statement.

Something like...

=IF(SUMPRODUCT(
    --($F$342:$F$372=8),
    --($G$342:$G$372="O"),
    --($H$342:$H$372="O"),
    --($I$342:$I$372="E"),
    --($K$342:$K$372="L"),
    --($L$342:$L$372="M"),
    --($M$342:$M$372="H")) >= X2,"Yes","")

If on Excel 2007 or later...

=IF(COUNTIFS(
     ($F$342:$F$372,8,
      $G$342:$G$372,"O",
      $H$342:$H$372,"O",
      $I$342:$I$372,"E",
      $K$342:$K$372,"L",
      $L$342:$L$372,"M",
      $M$342:$M$372="H") >= X2,"Yes","")

where X2 houses a value like 3...