Ignoring values in between brackets

[E1 calling]

Hello,

I want to count the number of cells containing a value (number) combined with a number in between brackets.

Only the number without the brackets should meet the criteria, the number in between the brackets should be ignored by the formula.

Example:

A1: 7(+1)

A2: 12(-3)

A3: 15(+2)

A4: 9(+8)

A5: 4(-1)

I’d like to count the cells with a value lower than 11, so the outcome should be 3. COUNTIF(A1:A5,”<11″) gives 0. What am I missing?

Thanks!

 

[Scott Huish]

You're missing that the numbers you are trying to count are not by themselves. However, according to your description, it should be 0, anway, as I am assuming by brackets you mean parentheses. There is no case in your sample where the criteria of a number without the brackets exist. So you either need to separate the values or have your formula do it for you.

I am going to assume you only want to count the values less than 11 regardless of the brackets.

=SUMPRODUCT(--(LEFT(A1:A5,FIND("(",A1:A5)-1)+0<11))

 

[E1 calling]

The formula works perfectly... thank you very much, much appreciated!

 

[E1 calling]

One more question though: what happens when the array contains regular numbers and hyvens?

Example:
A1: 7(+1)
A2: 12(-3)
A3: 15(+2)
A4: 9(+8)
A5: 4(-1)
A6: 5
A7: 9
A8: -
A9: 11
A10: -

I thought it was just a matter of adding COUNTIF in the formula (=SUMPRODUCT(--(LEFT(A1:A10,FIND("(",A1:A10)-1)+0<11))+COUNTIF(A1:A10,"<11"), but I get an error. What to do?

 

[Ruddles]

There is no case in your sample where the criteria of a number without the brackets exist.

I think by "without" he means "outside", as in: "There is a green hill far away without a city wall".

 

[Scott Huish]

=SUM(IF(A1:A10="-",0,IF((LEFT(A1:A10,FIND("(",A1:A10&"(")-1)+0<11),1)))

Confirm with CTRL-SHIFT-ENTER rather than just Enter.

Excel Workbook
	A	B	C	D
1	7(+1)			5
2	12(-3)
3	15(+2)
4	9(+8)
5	4(-1)
6	5
7	9
8	-
9	11
10	-
Sheet1

 

[E1 calling]

Many thanks for this. However the more I understand this, the more questions I have:

1. What is the exact meaning of the {} in a formula?
2. What happens with the formula with following additional values to the array?

A1: 7(+1)
A2: 12(-3)
A3: 15(+2)
A4: 9(+8)
A5: 4(-1)
A6: 5
A7: 9
A8: -
A9: 11
A10: -
A11:
A12: (x)
A13: #NA

A11 is an empty cell.

Thanks for taking time answering my questions

 

[Scott Huish]

For Question 1: It's an array formula. See here: http://www.cpearson.com/excel/ArrayFormulas.aspx

 

[E1 calling]

Thanks for pointing out the array formula, I will use it definitely more in the future.

For question 2 I am a bit stuck: the (x) and #NA are now part of the formula but I can't find how to ignore blank cells.

The formula I have looks now like this: =SUM(IF(A1:A13="-",0,IF(A1:A13="(x)",0,IF(A1:A13="#NA",0,IF((LEFT(A1:A13,FIND("(",A1:A13&"(")-1)+0<11),1)))))

The only missing bit is how to ignore any blank cell.

Many thanks for your help!

 

[Scott Huish]

=SUM(IF(ISNUMBER(LEFT(A1:A100,FIND("(",A1:A100&"(")-1)+0),(LEFT(A1:A100,FIND("(",A1:A100&"(")-1)+0<11)+0))
Confirm with CTRL-SHIFT-ENTER rather than just Enter.

Excel Workbook
	A	B	C
1	7(+1)		5
2	12(-3)
3	15(+2)
4	9(+8)
5	4(-1)
6	5
7	9
8	-
9	11
10	-
11	x
12	#NA
Sheet1