function showing false result when it is not

  • Thread starter Thread starter Amy-Marie
  • Start date Start date
A

Amy-Marie

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!!!
 
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!!!
Click to expand...

How do you know the zero is not the result of there being none of your
COUNTIF criteria?
 
Amy-Marie pretended :


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
Click to expand...

There are loads of 1's, it is just not counting them. The elements of
the formula are for the right ranges as well.
 
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 pretended :


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
Click to expand...

There are loads of 1's, it is just not counting them. The elements of
the formula are for the right ranges as well.
 
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
 
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".




There are loads of 1's, it is just not counting them. The elements of
the formula are for the right ranges as well.
Click to expand...

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
 
Amy-Marie see my other post
Cimjet
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" <[email protected]>
wrote in message




There are loads of 1's, it is just not counting them. The elements of
the formula are for the right ranges as well.
Click to expand...

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
 
Amy-Marie
Use "Sumproduct" function
=SUMPRODUCT(('Employee Details'!V4:V93="P",)*('Employee Details'!Y4:Y93=1))
 
I see an error in my formula,sorry. Try this one...
=SUMPRODUCT(('Employee Details'!V4:V93="P")*('Employee Details'!Y4:Y93=1))

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" <[email protected]>
wrote in message




There are loads of 1's, it is just not counting them. The elements of
the formula are for the right ranges as well.
Click to expand...

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
 
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))
Click to expand...

Yay it worked, thank you very much!!! :-)
 
You're welcome
Sorry about the typo..
Cimjet
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))
Click to expand...

Yay it worked, thank you very much!!! :-)
 
Back
Top