Eliminate count if condition is obeyed for more than once

Hi, all

I have these formula to count the cell ranges if the data inside obeys this specific condition, i.e. less or more than data in cell H6. Here's an example.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

How can I alter this formulae so that they will eliminate the count if the condition of (>H6) is obeyed in this manner:

H6 = 20

21, 18, 19, 22, 23, 24, 17, 16, 22

The count will be 3 instead of 5.

Anyone knows? Thanks in advance for the help!

It's not a good idea to try to communicate with us through formulas. Because of this, I'm not going to try to understand what your formulas are doing.

Given the numbers towards the bottom of your post (21, 18, 19, 22, 23, 24, 17, 16, 22), why should the count be 3? Which 3 numbers should be included and why?

The condition of count that has to be obeyed is when data in specific cell ranges is more than data in cell H6.

The count will only count 3 instead of 5 and the numbers are: 21, (22, 23, 24), 22.
22, 23 and 24 will be counted as once. To conclude, my formula should ignore if the condition is obeyed more than one time consecutively.

Hope that helps.

Using that sample of numbers, assuming that they are in A1:A9 and 20 is in cell C1, try this:

=SUMPRODUCT(--(FREQUENCY(IF(A1:A9>=C1,ROW(A1:A9)),IF(A1:A9<C1,ROW(A1:A9)))>0)) Ctrl Shift Enter

This formula will return 3.

If you are unable to adjust this to match your scenario, consider uploading a small representative sample of your workbook along with the desired result (manually entered).

To attach an Excel workbook, click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hereby I attached an example of data to be counted. In column B, the count formula is in cell B93 - if either range of B9:B32 or B39:B62 is more than 50, data in B69:B92 will be counted if they are more than 530 in H6.

Meanwhile, in E column, E39:E62 will be counted in E63 if they are less than 80 in cell I6.

How can I alter these formula to avoid counting if the condition in H6 and I6 are obeyed consecutively in succession?

Hoping for advices. Thanks in advance!

Try these:

B93 =SUMPRODUCT(--(FREQUENCY(IF(((B9:B32>50)+(B39:B62>50))*(B69:B92>H6),ROW(B69:B92)),IF(B69:B92<=H6,ROW(B69:B92)))>0)) Ctrl Shift Enter

E63 =SUMPRODUCT(--(FREQUENCY(IF((E9:E32=1)*(E39:E62<I6),ROW(E39:E62)),IF(E39:E62>=I6,ROW(E39:E62)))>0)) Ctrl Shift Enter