|
Help with Counting.
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 |
Help with Counting.
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 |
Help with Counting.
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 |
Help with Counting.
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 |
Help with Counting.
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 |
Help with Counting.
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 |
Help with Counting.
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 |
Help with Counting.
All the values in column E are numbers. all the other columns are text. I did
export these values from a database nut I don't think that would cause the problem, would it? "Peo Sjoblom" wrote: 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 |
Help with Counting.
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 On Jul 31, 4:11*pm, Dan wrote: All the values in column E are numbers. all the other columns are text. I did export these values from a database nut I don't think that would cause the problem, would it? "Peo Sjoblom" wrote: 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- Hide quoted text - - Show quoted text - |
Help with Counting.
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? "Pete_UK" wrote: 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 On Jul 31, 4:11 pm, Dan wrote: All the values in column E are numbers. all the other columns are text. I did export these values from a database nut I don't think that would cause the problem, would it? "Peo Sjoblom" wrote: 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- Hide quoted text - - Show quoted text - |
Help with Counting.
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 On Jul 31, 5:15*pm, Dan wrote: 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? "Pete_UK" wrote: 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 On Jul 31, 4:11 pm, Dan wrote: All the values in column E are numbers. all the other columns are text. I did export these values from a database nut I don't think that would cause the problem, would it? "Peo Sjoblom" wrote: 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- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Help with Counting.
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" wrote: 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 On Jul 31, 5:15 pm, Dan wrote: 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? "Pete_UK" wrote: 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 On Jul 31, 4:11 pm, Dan wrote: All the values in column E are numbers. all the other columns are text. I did export these values from a database nut I don't think that would cause the problem, would it? "Peo Sjoblom" wrote: 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- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Help with Counting.
Hi Dan,
you don't need the AND - the * is equivalent to AND, so you can have this: =SUMPRODUCT(('[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 this will count wherever column E is not zero AND column D is equal to "A" AND column G is between "A" and "D" inclusive. Hope this helps. Pete On Jul 31, 5:45*pm, Dan wrote: 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" wrote: 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 On Jul 31, 5:15 pm, Dan wrote: 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? "Pete_UK" wrote: 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 On Jul 31, 4:11 pm, Dan wrote: All the values in column E are numbers. all the other columns are text. I did export these values from a database nut I don't think that would cause the problem, would it? "Peo Sjoblom" wrote: 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- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| All times are GMT +1. The time now is 02:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com