OK, I have one more chalenge for you, I have to take this now and validate
off of even one more feild. I don't know if you can nest an and in the
sumproduct function or notbut this is kind of what I am trying to do.
=SUMPRODUCT(AND('[Inv
variance-q.xlsx]Inv_variance_q'!$E$2:$E$3317<>0)*('[Inv
variance-q.xlsx]Inv_variance_q'!$D$2:$D$3317="A")*(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")))
So anywhere that it is an A in column D and not zero in column E do the same
count. Any suggestions.
Pete_UK said:
Well, that's really an issue with your data, rather than the formula.
If you amended the formula to take account of all possible variations
in the data, it would become horrendous. If the * was in column E
(where a number is expected) then you could check the column using
ISNUMBER.
Hope this helps.
Pete thanks for your help, I found that I had a * in one of the feilds and
this was causing the error. Is there a way to eliminate this problem?
:
It is important that you compare like with like. You cannot do:
...)*(CODE('[Inv variance-q.xlsx]Inv_variance_q'!G$2:G
$3317)>="A")*(...
as CODE returns a number and you are trying to compare it with the
letter "A" - look again at the formula I gave you last night.
Pete
All the values in column E are numbers. all the other columns aretext. I did
export these values from a database nut I don't think that would cause the
problem, would it?
:
You probably have text in this range
E$2:E$3317
--
Regards,
Peo Sjoblom
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.
:
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.
Pete, Thanks for the info I have tried this but it doesn'tseem 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"))
:
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
I need some help with a Count, An example below. I needto 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- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -
