I not very use to use excel complex formula. Here is request Column has few number (-ve/ ve). I have to count these based on intervals. These interval are not pre-decided. See screen Shot

Values in Label col can change in run time. A is less than -15, B is between -15 to 6 and so on. I have to create a formula to add a count in the Count col.

Please guide

Thank you Regards

CodePudding user response：

You will save yourself a lot of maintenance headaches by re-formatting the "Labels" Table:

The formula to place in the first count cell (G7 in my example) is:

=COUNTIFS(A$1:A$19,">="&E7,A$1:A$19,"<="&F7)

And then fill it down the length of the table. (In this example 4 rows). Be mindful of the $ that lock the rows of your values column.

CodePudding user response：

I would strongly advise to follow the approach outlined by @Max as it is easier to maintain and less error-prone. However, as you stated, that you are looking for a solution that takes your format into account, I come up with this.

Note: I split up the complex formula in different pieces to make it easier to explain. You can paste the different pieces together in a single cell per row if you like.

Step 1: Getting rid of unnecessary descriptions

I use a combination of 'SUBSTITUTE', 'MID' and 'FIND' to extract our operators and values from your labels: =SUBSTITUTE(MID(D3,FIND("(",D3) 1,FIND(")",D3)-FIND("(",D3)-1),"interval ",""). The cell D3 contains your label, e.g. "A (interval <-15).

Step 2: Getting the lower threshold

I check whether the label contains two thresholds or not by looking for the "/" character. Next, I handle both situations. =IF(ISERR(FIND("/",F3)),MID(F3,1,LENGTH(F3)),">="&MID(F3,1,FIND("/",F3))) The cell F3`contains the result of Step 1

Step 4: Getting the upper threshold

Similar to Step 2, except for the operators. =IF(ISERR(FINN("/",F3)),MID(F3,1,LENGTH(F3)),"<="&MID(F3,FIND("/",F3),LENGTH(F3)-FIND("/",F2)))

Step 5: Counting

I use MID to include the single pieces of Step 3 and Step 4 as text into the formula. If you paste everything together, the use of it will not be necessary. =COUNTIFS($A$1:$A$19,MID(G3,1,LENGTH(G3)),$A$1:$A$19,MID(H3,1,LENGTH(H3)))

As I am in a hurry, I cannot give further explanations nor screenshots, yet, but I am sure, you can get along with it.