New to excel formulas and need help with a problem.

example:

A B
3324 jpr
2756 jpr
3324 jpr
2856 slt
3492 slt
5864 bdg
2856 slt
2756 jpr

I am trying to get a count of the jpr's in col B , that have a
different number in col A.
the solution in this example should be 2 for jpr.

Hi

One way
Use a helper column with the formula, say in C1
=--(AND(B1="jpr",COUNTIF($A$1:A1,A1)=1))
copy down as far as required
=SUM(C:C)
--
Regards

Roger Govier


wrote in message
oups.com...
New to excel formulas and need help with a problem.

example:

A B
3324 jpr
2756 jpr
3324 jpr
2856 slt
3492 slt
5864 bdg
2856 slt
2756 jpr

I am trying to get a count of the jpr's in col B , that have a
different number in col A.
the solution in this example should be 2 for jpr.

Thanks for the help!

Is there a place that will show me what each command in that formula
means, or is it just a thing you have to remeber?

Hi

Breaking it down
=--(AND(B1="jpr",COUNTIF($A$1:A1,A1)=1))

B1="jpr" this tests whether the value in B1 is the same as your
required string "jpr"
You could have put jpr in another cell e.g. cell D1 and used
B1=$D$1 the dollar signs would fix D1 as absolute, and it would not
alter as you copy down the range.

COUNTIF($A$1:A1,A1) this counts the number of times that the value in
cell A1 occurs within the range $A$1:A1
On the first line, it can only be 1 or 0, because we are only counting
against a range of 1 cell - A1
As you copy down, because the first reference to the range is Absolute,
$A$1 and the second is Relative A1 and the argument is relative, A1,
then as you copy down it becomes
COUNTIF($A$1:A2,A2)
which is now counting the number of times the value in A2 occurs within
the longer list of A1:A2. This will increase as you go down the range

Now we put the two things together with an AND statement, and a further
condition, we only want results where the value returned from the
COUNTIF() formula = 1
=AND(B1="jpr",COUNTIF($A$1:A1,A1)=1)
So if BOTH case are true we will get a result of TRUE otherwise the
result will be FALSE
This will only give a result of 1, the first time it finds "jpr" and
3324 for example, as when that combination occurs further down the list,
the result of the COUNTIF will be greater than 1, hence the final
outcome will be FALSE.

Finally we use the double unary minus -- to wrap the whole formula and
this coerces the TRUE value to a 1 and the FALSES to 0

Summing the values in column C then tells us how many times we had the
result we were seeking which is 2


Setting all this out makes me realise I could have expressed the formula
more simply
= (COUNTIF($A$1:A1,A1)=1) * (B1="jpr")
Using the logic as above the first part returns TRUE or FALSE, the
second part returns TRUE or FALSE and multiplying them together does the
coercion, so we get or result of 1 or 0 for each cell and summing the
range in column C gives our result of 2.
--
Regards

Roger Govier


wrote in message
ups.com...

Thanks for the help!

Is there a place that will show me what each command in that formula
means, or is it just a thing you have to remeber?