Mid function in an array

[Barnapkin]

I get an excel error with the following formula:
+COUNTIF(MID(P21,1,2),">0")

Lets say cell P21 has the following inside the cell:
31-V32

When I look at the results in the formula editor, I see 31 and ">0" in the appropriate rows.
The formuila should return a value of 1.


I want the formula to eventually be used for a range of cells.
Lets say the listed cells have the following values:
P21=31-V32, Q21=03-X55, R21=31-A01

Thought the derivitive formula would be something like:
+COUNTIF(MID(P21:R21,1,2),">0")

Thanks in advance.

John

[Ace_XL]

Sumproduct would help in such cases since COUNTIF does not recognise the results of the MID function as an array

Please Login or Register  to view this content.

[JosephP]

COUNTIF requires a range as its first argument-it will not work with an array there

[daddylonglegs]

Can you give some examples where the first two characters wouldn't be ">0"? Do you mean there would be letters?

[Moo the Dog]

Firstly - if you are using MID but starting at the first character, just use LEFT.
Secondly - when you extract the first two characters, they are treated as text, so if you want to perform a > operation on them, you need to perform a mathematical operation on them in the formula (I like to use *1)

This would be the formula for one cell: =IF(LEFT(P21,2)*1>0,1) applied as a regular formula (Enter)
This would be the formula for multiple cells: =COUNT(IF(LEFT(P21:R21,2)*1>0,1)) applied as an array formula (Ctrl + Shift + Enter)

- Moo

[K m]

There are a few problems with your data:
- MID(P21,1,2) is the same as =Left(P21,2)
- MID returns text. You are trying to compare text to numbers. By adding a zero, text can be converted to numbers. For example,
=(MID(P21,1,2)+0) returns the numeric equivalent of the text

[Barnapkin]

The result I get is zero. I expect a value of 3. I do see 3 true states in the formula editor.
The attached is a snip from the screen.

Thank you very much for the try.

sumproductfail.GIF

[Barnapkin]

Moo, you are closest so far.

The first screen snip shows the equation you suggested in the formula bar and the populated cells.
The second screen snip shows the formula editor indicating the correct answer 2, but as you can see in the first snip the displayed value is zero.
What gives with that?

CountIfLeftFailA.GIF
CountIfLeftFailB.GIF

I do intend to do something with the 3 rightmost characters once I get the left side sorted.


Thank you for your time

[Moo the Dog]

The problem you are having with the SUMPRODUCT formula is you left out the "--". They are required. Your formula for that image should be:

=SUMPRODUCT(--(MID(P22:R22,1,2)*1>0))

- Moo

[Moo the Dog]

The reason why you are seeing 0 when you use my formula [ =COUNT(IF(LEFT(P21:R21,2)*1>0,1)) ] is because you need to apply it as an ARRAY formula. This means that after you type in the formula in the formula bar, you must hold down the Ctrl + Shift keys and hit Enter. You will know when you've entered it correctly because brackets appear {formula} <-- like that around the formula. Do NOT enter the brackets manually... it won't work that way.

- Moo

[Ace_XL]

Just in case you have blank cells use this modified verison of my forumla in Post #2

Please Login or Register  to view this content.

[Barnapkin]

When I looked in the help for the subproduct there were no -- or mention of it being required in the instructions. What is the purpose of --?

It did work by the way, but only if the contents of every cell was populated.

Thank you.

[Barnapkin]

LOL, I just commented on that. Great!!

[Ace_XL]

When I looked in the help for the subproduct there were no -- or mention of it being required in the instructions. What is the purpose of --?

Have a look here ..
http://www.ozgrid.com/forum/showthread.php?t=54281