Count the number of records conditionally

[leewcrawford]

Is there a formula that I can use to Count how may records exist in a group.

Example. I have records labled M right, M left, N right, N left,.....U right, U left. Each letter represents a server cabinet

In a input cell, I can select M right.
In a second cell, I can select U left.

I need to have the formula calculate the number of Cabinets'
(M, N, O, P, Q, R, S, T, U) that it takes to cross the cabinets = 9

The problem I am having is that the formula has to change based on weather they select the left side or right side of the cab.

Example.
First input cell = M right
Second input cell = U right

I need to have the formula calculate the number of cabinets
(M,N,O,P,Q,R,S,T) that it takes to cross the cabinets' Note: U is not counted. = 8

I would be grateful for any help.

[NBVC]

Why isn't U counted in the latter sample

[leewcrawford]

Why isn't U counted in the latter sample

Each entry represents a cabinet. I am eventually going to write a formula that will use the count of the entries to provide a cable length to connect cabs.

If you you are running a cable from the right side of one cab. to the left side of the one next to it you would only cross one cab.

In the case of M right to u left you cross all the cabs with the exception of U.

[NBVC]

Perhaps:

=COLUMN(INDIRECT(LEFT(B1)&1))-COLUMN(INDIRECT(LEFT(A1)&1))+IF(MID(B1,3,255)="right",1,0)

where A1 and B1 are your input cells.

[ChemistB]

=CODE("U")-CODE("M")

or if U is in B2 and M is in A2

=CODE(B2)-CODE(A2)

Is that what you are after?

[leewcrawford]

Actually the code that NBVC supplied wored great. However I do not know how it works. I cut and pasted it into the sheet and get the results that I am looking for. Is there any way you can break down how your code works NBVC?

[NBVC]

Actually, mine is a little more complicated than it has to be...

Combining ChemistB's formula with mine should give you the same result and be less volatile.


Try instead:

=CODE(LEFT(B1))-CODE(LEFT(A1))+IF(MID(B1,3,255)="right",1,0)

The Code() function returns a numeric code based on ANSI standards that represents the text character inside the brackets (in this case the Left-most character in the strings within A1 & B1).

Once the difference is found, the IF(MID()) function looks to see if the remainder of the text in B1 is "Right". If it is, then it crosses that cabinet too and therefore adds 1 to the result... otherwise it doesn't cross the cabinet and it adds 0.

Hope that helps.

[leewcrawford]

That is great. It is much easier to usderstand!

I just realized however that if I enter the start and end locations in reverse order.

Example: (Normal order) M right ----> U left = works great
(reverse order) Uleft -----> M right = neg number returned and is 1 off

Is there a way to have the neg. number changed to a posative number and the "right" at the end of the formula changed to left if the inputs are in reverse order?

=CODE(LEFT(B1))-CODE(LEFT(A1))+IF(MID(B1,3,255)="right",1,0)

[NBVC]

Try:

=IF(CODE(LEFT(B1))>CODE(LEFT(A1)),CODE(LEFT(B1))-CODE(LEFT(A1))+IF(MID(B1,3,255)="right",1,0),CODE(LEFT(A1))-CODE(LEFT(B1))+IF(MID(A1,3,255)="right",1,0))

Note: The Code() function is sensitive to case... I.e. both letters in A1 and B1 have to be the same case or you will get erroneous results...

If you want to ensure the same case...surround each cell reference within the CODE() function with the UPPER() function... e.g. CODE(LEFT(UPPER(A1)))

[leewcrawford]

That works. NBVC, Thanks so much. I know you have answered plenty of my posts. I have been learning so much about excel. This Forum ROCKS!!!