| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function showing false result when it is not
Can anyone see what is wrong with this function? =IF('Employee
Details'!V4:V93="P",COUNTIF('Employee Details'!Y4:Y93,1),0) - There are loads of "P"'s in that range but it is still showing 0 as if it is false. How this makes sense. Its driving me mad!!! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
function showing false result when it is not
Amy-Marie pretended :
Can anyone see what is wrong with this function? =IF('Employee Details'!V4:V93="P",COUNTIF('Employee Details'!Y4:Y93,1),0) - There are loads of "P"'s in that range but it is still showing 0 as if it is false. How this makes sense. Its driving me mad!!! How do you know the zero is not the result of there being none of your COUNTIF criteria? -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
function showing false result when it is not
On Jan 30, 7:49*pm, GS wrote:
Amy-Marie pretended : Can anyone see what is wrong with this function? =IF('Employee Details'!V4:V93="P",COUNTIF('Employee Details'!Y4:Y93,1),0) - There are loads of "P"'s in that range but it is still showing 0 as if it is false. How this makes sense. Its driving me mad!!! How do you know the zero is not the result of there being none of your COUNTIF criteria? -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc There are loads of 1's, it is just not counting them. The elements of the formula are for the right ranges as well. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
function showing false result when it is not
Hi Amy-Marie
What you have is an array formula. You enter an array formula by pressing ctrl+shift+Enter instead of just Enter. If you do it right; Excel will display an array formula surrounded by curly braces in the Formula Bar. You cannot type the curly braces yourself. If you make a mistake, select the cell, double click on it to go to Edit mode or press F2 and edit as needed, then press ctrl+shift+Enter. But I'm not sure that's what you want. Your formula will count all the "1" if there is 1 "P". If it's not what you want, explain it in detail. "Amy-Marie" wrote in message ... On Jan 30, 7:49 pm, GS wrote: Amy-Marie pretended : Can anyone see what is wrong with this function? =IF('Employee Details'!V4:V93="P",COUNTIF('Employee Details'!Y4:Y93,1),0) - There are loads of "P"'s in that range but it is still showing 0 as if it is false. How this makes sense. Its driving me mad!!! How do you know the zero is not the result of there being none of your COUNTIF criteria? -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc There are loads of 1's, it is just not counting them. The elements of the formula are for the right ranges as well. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
function showing false result when it is not
Hi again
If your trying to count the (1's) matching the letter P on the same row then maybe this is what you want. =SUMPRODUCT(('Employee Details'!V4:V93="P",)*('Employee Details'!Y4:Y93=1)) Cimjet "Amy-Marie" wrote in message ... Can anyone see what is wrong with this function? =IF('Employee Details'!V4:V93="P",COUNTIF('Employee Details'!Y4:Y93,1),0) - There are loads of "P"'s in that range but it is still showing 0 as if it is false. How this makes sense. Its driving me mad!!! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
function showing false result when it is not
On Jan 30, 8:11*pm, "Cimjet" wrote:
Hi Amy-Marie What you have is an array formula. You enter an array formula by pressing ctrl+shift+Enter instead of just Enter. *If you do it right; Excel will display an array formula surrounded by curly braces in the Formula Bar. *You cannot type the curly braces yourself. * If you make a mistake, select the cell, double click on it to go to Edit mode or press F2 and edit as needed, then press ctrl+shift+Enter. But I'm not sure that's what you want. Your formula will count all the "1" if there is 1 "P". If it's not what you want, explain it in detail."Amy-Marie" wrote in message ... On Jan 30, 7:49 pm, GS wrote: Amy-Marie pretended : Can anyone see what is wrong with this function? =IF('Employee Details'!V4:V93="P",COUNTIF('Employee Details'!Y4:Y93,1),0) - There are loads of "P"'s in that range but it is still showing 0 as if it is false. How this makes sense. Its driving me mad!!! How do you know the zero is not the result of there being none of your COUNTIF criteria? -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc There are loads of 1's, it is just not counting them. The elements of the formula are for the right ranges as well. Cimjet, I tried ctrl,shift and enter and the curly braces appeared and it then counted ALL of the 1's rather than just the 1's that have a "P" in the cell next to it. All I want is the formula to count all the 1's that have a P in the cell next to it only. Does that make sense? Many thanks Amy |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
function showing false result when it is not
Amy-Marie see my other post
Cimjet "Amy-Marie" wrote in message ... On Jan 30, 8:11 pm, "Cimjet" wrote: Hi Amy-Marie What you have is an array formula. You enter an array formula by pressing ctrl+shift+Enter instead of just Enter. If you do it right; Excel will display an array formula surrounded by curly braces in the Formula Bar. You cannot type the curly braces yourself. If you make a mistake, select the cell, double click on it to go to Edit mode or press F2 and edit as needed, then press ctrl+shift+Enter. But I'm not sure that's what you want. Your formula will count all the "1" if there is 1 "P". If it's not what you want, explain it in detail."Amy-Marie" wrote in message ... On Jan 30, 7:49 pm, GS wrote: Amy-Marie pretended : Can anyone see what is wrong with this function? =IF('Employee Details'!V4:V93="P",COUNTIF('Employee Details'!Y4:Y93,1),0) - There are loads of "P"'s in that range but it is still showing 0 as if it is false. How this makes sense. Its driving me mad!!! How do you know the zero is not the result of there being none of your COUNTIF criteria? -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc There are loads of 1's, it is just not counting them. The elements of the formula are for the right ranges as well. Cimjet, I tried ctrl,shift and enter and the curly braces appeared and it then counted ALL of the 1's rather than just the 1's that have a "P" in the cell next to it. All I want is the formula to count all the 1's that have a P in the cell next to it only. Does that make sense? Many thanks Amy |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
function showing false result when it is not
Amy-Marie
Use "Sumproduct" function =SUMPRODUCT(('Employee Details'!V4:V93="P",)*('Employee Details'!Y4:Y93=1)) "Cimjet" wrote in message ... Amy-Marie see my other post Cimjet "Amy-Marie" wrote in message ... On Jan 30, 8:11 pm, "Cimjet" wrote: Hi Amy-Marie What you have is an array formula. You enter an array formula by pressing ctrl+shift+Enter instead of just Enter. If you do it right; Excel will display an array formula surrounded by curly braces in the Formula Bar. You cannot type the curly braces yourself. If you make a mistake, select the cell, double click on it to go to Edit mode or press F2 and edit as needed, then press ctrl+shift+Enter. But I'm not sure that's what you want. Your formula will count all the "1" if there is 1 "P". If it's not what you want, explain it in detail."Amy-Marie" wrote in message ... On Jan 30, 7:49 pm, GS wrote: Amy-Marie pretended : Can anyone see what is wrong with this function? =IF('Employee Details'!V4:V93="P",COUNTIF('Employee Details'!Y4:Y93,1),0) - There are loads of "P"'s in that range but it is still showing 0 as if it is false. How this makes sense. Its driving me mad!!! How do you know the zero is not the result of there being none of your COUNTIF criteria? -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc There are loads of 1's, it is just not counting them. The elements of the formula are for the right ranges as well. Cimjet, I tried ctrl,shift and enter and the curly braces appeared and it then counted ALL of the 1's rather than just the 1's that have a "P" in the cell next to it. All I want is the formula to count all the 1's that have a P in the cell next to it only. Does that make sense? Many thanks Amy |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
function showing false result when it is not
I see an error in my formula,sorry. Try this one...
=SUMPRODUCT(('Employee Details'!V4:V93="P")*('Employee Details'!Y4:Y93=1)) "Amy-Marie" wrote in message ... On Jan 30, 8:11 pm, "Cimjet" wrote: Hi Amy-Marie What you have is an array formula. You enter an array formula by pressing ctrl+shift+Enter instead of just Enter. If you do it right; Excel will display an array formula surrounded by curly braces in the Formula Bar. You cannot type the curly braces yourself. If you make a mistake, select the cell, double click on it to go to Edit mode or press F2 and edit as needed, then press ctrl+shift+Enter. But I'm not sure that's what you want. Your formula will count all the "1" if there is 1 "P". If it's not what you want, explain it in detail."Amy-Marie" wrote in message ... On Jan 30, 7:49 pm, GS wrote: Amy-Marie pretended : Can anyone see what is wrong with this function? =IF('Employee Details'!V4:V93="P",COUNTIF('Employee Details'!Y4:Y93,1),0) - There are loads of "P"'s in that range but it is still showing 0 as if it is false. How this makes sense. Its driving me mad!!! How do you know the zero is not the result of there being none of your COUNTIF criteria? -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc There are loads of 1's, it is just not counting them. The elements of the formula are for the right ranges as well. Cimjet, I tried ctrl,shift and enter and the curly braces appeared and it then counted ALL of the 1's rather than just the 1's that have a "P" in the cell next to it. All I want is the formula to count all the 1's that have a P in the cell next to it only. Does that make sense? Many thanks Amy |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
function showing false result when it is not
On Jan 30, 8:21*pm, "Cimjet" wrote:
Hi again If your trying to count the (1's) matching the letter P on the same row then maybe this is what you want. =SUMPRODUCT(('Employee Details'!V4:V93="P",)*('Employee Details'!Y4:Y93=1)) Cimjet"Amy-Marie" wrote in message ... Can anyone see what is wrong with this function? =IF('Employee Details'!V4:V93="P",COUNTIF('Employee Details'!Y4:Y93,1),0) - There are loads of "P"'s in that range but it is still showing 0 as if it is false. How this makes sense. Its driving me mad!!! Yay it worked, thank you very much!!! :-) |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
function showing false result when it is not
You're welcome
Sorry about the typo.. Cimjet "Amy-Marie" wrote in message ... On Jan 30, 8:21 pm, "Cimjet" wrote: Hi again If your trying to count the (1's) matching the letter P on the same row then maybe this is what you want. =SUMPRODUCT(('Employee Details'!V4:V93="P",)*('Employee Details'!Y4:Y93=1)) Cimjet"Amy-Marie" wrote in message ... Can anyone see what is wrong with this function? =IF('Employee Details'!V4:V93="P",COUNTIF('Employee Details'!Y4:Y93,1),0) - There are loads of "P"'s in that range but it is still showing 0 as if it is false. How this makes sense. Its driving me mad!!! Yay it worked, thank you very much!!! :-) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Result = to 0 says false | Excel Worksheet Functions | |||
| Logical Format showing 0/1 and not True/False. | Excel Discussion (Misc queries) | |||
| false result | Excel Worksheet Functions | |||
| Can Excel operate a function based on a true or false result? | Excel Worksheet Functions | |||
| Excel - saving result of a function without showing the function | Excel Programming | |||
Linear Mode
