Counting cells in excel

[sdelacey]

Hi Guys

Could you please help. I am trying to write a formula in Excel that will count two cells that match a certain critiea.

If the first cell contains a letter followed by a number, eg B98 and the second cell just as a number eg 1102, I need to count the cells (as one) if the cell contains B and the second cell is above 0. Thank You for your help,

SDelacey

 

[SkipVought]

Hi,

not sure if i understood your problem but...
[tt]
=IF(AND(NOT(ISNUMBER(VALUE(LEFT(A1,1)))),ISNUMBER(VALUE(RIGHT(A1,LEN(A1)-1))),ISNUMBER(B1)),1,0)
[/tt]
assuming that the 2 values are in A1 & B1

???

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[sdelacey]

Morning

What I need is in column A i have a set of data which starts with a letter and followed by a series of numbers, in column B the data is amounts to the corresponding code from column a. I am trying to write a fomula that sees the first letter of column A (which is 7 chars) and is counted if the amount is above 0 for example.

A B

B12-444 1023
B98-456 124
C58-879 1256
B11-489 0

Therefore the resulting count should be 2, because 3 start with the letter B, and 2 of them have amounts of above 0.

I hope this is a little clearer.

 

[xlbo]

=sumproduct((left(A1:A100,1)="B")*(B1:B100>0))

should do the trick - amend range lengths to suit

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question