I need some help with a Count, An example below. I need to count all the
lines that equal zero and where the aisle is between a-d. I will also need
one that adds in another feild of value class. So this one will get me the
total variance count per aisle but I will also nee dto know the variance per
value class and aisle. Thanks
Variance Aisle
-8 R
2 O
0 Q
0 L
0 J
-1 L
0 O
-38 J
10 K
4 O
0 O
0 L
-1 L
0 G
0 G
0 O
0 E

Assuming your data is in A2:A17, try this:

=SUMPRODUCT((A2:A17=0)*(CODE(B2:B17)=CODE("A"))*( CODE(B2:B17)<=CODE("L")))

I've deliberately left the two letters in the formula (A and L in this
case), so that you can easily change them if you wish.

Hope this helps.

Pete


On Jul 30, 5:28*pm, Dan wrote:
I need some help with a Count, An example below. I need to count all the
lines that equal zero and where the aisle is between a-d. I will also need
one that adds in another feild of value class. So this one will get me the
total variance count per aisle but I will also nee dto know the variance per
value class and aisle. Thanks
Variance * * * *Aisle
-8 * * *R
2 * * * O
0 * * * Q
0 * * * L
0 * * * J
-1 * * *L
0 * * * O
-38 * * J
10 * * *K
4 * * * O
0 * * * O
0 * * * L
-1 * * *L
0 * * * G
0 * * * G
0 * * * O
0 * * * E

Pete, Thanks for the info I have tried this but it doesn't seem to be
working, could you take a look.
=SUMPRODUCT(('[Inv
variance-q.xlsx]Inv_variance_q'!E$2:E$3317<0)*(CODE('[Inv
variance-q.xlsx]Inv_variance_q'!G$2:G$3317)="A")*(CODE('[Inv
variance-q.xlsx]Inv_variance_q'!G$2:G$3317)<="D"))

"Pete_UK" wrote:

Assuming your data is in A2:A17, try this:

=SUMPRODUCT((A2:A17=0)*(CODE(B2:B17)=CODE("A"))*( CODE(B2:B17)<=CODE("L")))

I've deliberately left the two letters in the formula (A and L in this
case), so that you can easily change them if you wish.

Hope this helps.

Pete


On Jul 30, 5:28 pm, Dan wrote:
I need some help with a Count, An example below. I need to count all the
lines that equal zero and where the aisle is between a-d. I will also need
one that adds in another feild of value class. So this one will get me the
total variance count per aisle but I will also nee dto know the variance per
value class and aisle. Thanks
Variance Aisle
-8 R
2 O
0 Q
0 L
0 J
-1 L
0 O
-38 J
10 K
4 O
0 O
0 L
-1 L
0 G
0 G
0 O
0 E

In Pete's formula the first comparison is equal to zero, in your formula,
it's *not* equal to zero. This could be your problem.

If not, when you post back, elaborate on what "not working" means. What
result did you get, and what did you expect?

Regards,
Fred.

"Dan" wrote in message
...
Pete, Thanks for the info I have tried this but it doesn't seem to be
working, could you take a look.
=SUMPRODUCT(('[Inv
variance-q.xlsx]Inv_variance_q'!E$2:E$3317<0)*(CODE('[Inv
variance-q.xlsx]Inv_variance_q'!G$2:G$3317)="A")*(CODE('[Inv
variance-q.xlsx]Inv_variance_q'!G$2:G$3317)<="D"))

"Pete_UK" wrote:

Assuming your data is in A2:A17, try this:

=SUMPRODUCT((A2:A17=0)*(CODE(B2:B17)=CODE("A"))*( CODE(B2:B17)<=CODE("L")))

I've deliberately left the two letters in the formula (A and L in this
case), so that you can easily change them if you wish.

Hope this helps.

Pete


On Jul 30, 5:28 pm, Dan wrote:
I need some help with a Count, An example below. I need to count all
the
lines that equal zero and where the aisle is between a-d. I will also
need
one that adds in another feild of value class. So this one will get me
the
total variance count per aisle but I will also nee dto know the
variance per
value class and aisle. Thanks
Variance Aisle
-8 R
2 O
0 Q
0 L
0 J
-1 L
0 O
-38 J
10 K
4 O
0 O
0 L
-1 L
0 G
0 G
0 O
0 E

Fred, when I was sying that it was not working I meant it is still giving me
a #VALUE! error in the cell. I would like it to give me a count where it is
equalt to zero and where it is not equal to zero in my data. I need it to
work both ways.

"Fred Smith" wrote:

In Pete's formula the first comparison is equal to zero, in your formula,
it's *not* equal to zero. This could be your problem.

If not, when you post back, elaborate on what "not working" means. What
result did you get, and what did you expect?

Regards,
Fred.

"Dan" wrote in message
...
Pete, Thanks for the info I have tried this but it doesn't seem to be
working, could you take a look.
=SUMPRODUCT(('[Inv
variance-q.xlsx]Inv_variance_q'!E$2:E$3317<0)*(CODE('[Inv
variance-q.xlsx]Inv_variance_q'!G$2:G$3317)="A")*(CODE('[Inv
variance-q.xlsx]Inv_variance_q'!G$2:G$3317)<="D"))

"Pete_UK" wrote:

Assuming your data is in A2:A17, try this:

=SUMPRODUCT((A2:A17=0)*(CODE(B2:B17)=CODE("A"))*( CODE(B2:B17)<=CODE("L")))

I've deliberately left the two letters in the formula (A and L in this
case), so that you can easily change them if you wish.

Hope this helps.

Pete


On Jul 30, 5:28 pm, Dan wrote:
I need some help with a Count, An example below. I need to count all
the
lines that equal zero and where the aisle is between a-d. I will also
need
one that adds in another feild of value class. So this one will get me
the
total variance count per aisle but I will also nee dto know the
variance per
value class and aisle. Thanks
Variance Aisle
-8 R
2 O
0 Q
0 L
0 J
-1 L
0 O
-38 J
10 K
4 O
0 O
0 L
-1 L
0 G
0 G
0 O
0 E

You probably have text in this range

E$2:E$3317


--


Regards,


Peo Sjoblom

"Dan" wrote in message
...
Fred, when I was sying that it was not working I meant it is still giving
me
a #VALUE! error in the cell. I would like it to give me a count where it
is
equalt to zero and where it is not equal to zero in my data. I need it to
work both ways.

"Fred Smith" wrote:

In Pete's formula the first comparison is equal to zero, in your formula,
it's *not* equal to zero. This could be your problem.

If not, when you post back, elaborate on what "not working" means. What
result did you get, and what did you expect?

Regards,
Fred.

"Dan" wrote in message
...
Pete, Thanks for the info I have tried this but it doesn't seem to be
working, could you take a look.
=SUMPRODUCT(('[Inv
variance-q.xlsx]Inv_variance_q'!E$2:E$3317<0)*(CODE('[Inv
variance-q.xlsx]Inv_variance_q'!G$2:G$3317)="A")*(CODE('[Inv
variance-q.xlsx]Inv_variance_q'!G$2:G$3317)<="D"))

"Pete_UK" wrote:

Assuming your data is in A2:A17, try this:

=SUMPRODUCT((A2:A17=0)*(CODE(B2:B17)=CODE("A"))*( CODE(B2:B17)<=CODE("L")))

I've deliberately left the two letters in the formula (A and L in this
case), so that you can easily change them if you wish.

Hope this helps.

Pete


On Jul 30, 5:28 pm, Dan wrote:
I need some help with a Count, An example below. I need to count all
the
lines that equal zero and where the aisle is between a-d. I will
also
need
one that adds in another feild of value class. So this one will get
me
the
total variance count per aisle but I will also nee dto know the
variance per
value class and aisle. Thanks
Variance Aisle
-8 R
2 O
0 Q
0 L
0 J
-1 L
0 O
-38 J
10 K
4 O
0 O
0 L
-1 L
0 G
0 G
0 O
0 E

I have also tried it this way.Still not working.
=SUMPRODUCT(('[Inv
variance-q.xlsx]Inv_variance_q'!E$2:E$3317<0)*(CODE('[Inv
variance-q.xlsx]Inv_variance_q'!G$2:G$3317)=CODE("A"))*(CODE('[Inv
variance-q.xlsx]Inv_variance_q'!G$2:G$3317)<=CODE("D")))

"Pete_UK" wrote:

Assuming your data is in A2:A17, try this:

=SUMPRODUCT((A2:A17=0)*(CODE(B2:B17)=CODE("A"))*( CODE(B2:B17)<=CODE("L")))

I've deliberately left the two letters in the formula (A and L in this
case), so that you can easily change them if you wish.

Hope this helps.

Pete


On Jul 30, 5:28 pm, Dan wrote:
I need some help with a Count, An example below. I need to count all the
lines that equal zero and where the aisle is between a-d. I will also need
one that adds in another feild of value class. So this one will get me the
total variance count per aisle but I will also nee dto know the variance per
value class and aisle. Thanks
Variance Aisle
-8 R
2 O
0 Q
0 L
0 J
-1 L
0 O
-38 J
10 K
4 O
0 O
0 L
-1 L
0 G
0 G
0 O
0 E