function showing false result when it is not

[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!!!

 

[GS]

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?

 

[Amy-Marie]

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

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]

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

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]

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]

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.

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

 

[Cimjet]

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.

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

 

[Cimjet]

Amy-Marie
Use "Sumproduct" function
=SUMPRODUCT(('Employee Details'!V4:V93="P",)*('Employee Details'!Y4:Y93=1))

 

[Cimjet]

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.

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]

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))

Yay it worked, thank you very much!!!

 

[Cimjet]

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))

Yay it worked, thank you very much!!!