EXCEL : Formula help request

[army20]

Hello All

I need help writing a a formula in MS Excel 2003.

I want the cell "J1" to display how many "P" letter in the line from "B1" to "I1"

Thanks !

-army

[Must4ng]

Search for "COUNTIF' in the help-there's a boatload of examples to get you started.

[army20]

I can't figure out

[clavicle]

write formula in J1

=COUNTIF(B1:I1, "p")

To count p, which is a text value, enclose in quotes

Edited March 1, 2006 by clavicle

[army20]

hoooo that was too easy !

@clavicle: thanks a lot

[clavicle]

I am glad I could help!

[army20]

Is there any way to leave the cell blank or to display 0 when the result of the formula equal #DIV/0! ?????

-army

[army20]

can I simplify this formula: =COUNTIF(G3:GG3;"p")/(COUNTIF(G3:GG3;"p")+COUNTIF(G3:GG3;"n")+COUNTIF(G3:GG3;"a"))

it says: Give me the number of P and divide it by the total number of time the letter P,A,N are shown

[clavicle]

It probably won't be possible, because you can put 0 where there is error as #DIV/0! is not equal to 0

Getting blank is also not possible, becasue excel is calculating it as an error, otherwise, conditional formating is the solution for any error free value, say 0.

Edited March 1, 2006 by clavicle

[clavicle]

=COUNTIF(G3:GG3;"p")/(COUNTIF(G3:GG3;"p")+COUNTIF(G3:GG3;"n")+COUNTIF(G3:GG3;"a")

change to

=COUNTIF(G3:GG3;"p")/(COUNTIF(G3:GG3;"p")+COUNTIF(G3:GG3;"n")+COUNTIF(G3:GG3;"a"))

You are missing out closing brace to make whole denominator one value

[jaclaz]

Actually it is possible by enclosing the formula in an IF statement, using the ISERR function:

This will give 0 if an error is the result:

=IF(ISERR(COUNTIF(G3:GG3;"p")/(COUNTIF(G3:GG3;"p")+COUNTIF(G3:GG3;"n")+COUNTIF(G3:GG3;"a")));0;COUNTIF(G3:GG3;"p")/(COUNTIF(G3:GG3;"p")+COUNTIF(G3:GG3;"n")+COUNTIF(G3:GG3;"a")))

This will give blank if an error is the result:

=IF(ISERR(COUNTIF(G3:GG3;"p")/(COUNTIF(G3:GG3;"p")+COUNTIF(G3:GG3;"n")+COUNTIF(G3:GG3;"a")));"";COUNTIF(G3:GG3;"p")/(COUNTIF(G3:GG3;"p")+COUNTIF(G3:GG3;"n")+COUNTIF(G3:GG3;"a")))

This will prevent ONLY #DIV/0!:

=IF((COUNTIF(G3:GG3;"p")+COUNTIF(G3:GG3;"n")+COUNTIF(G3:GG3;"a"))=0;"";COUNTIF(G3:GG3;"p")/(COUNTIF(G3:GG3;"p")+COUNTIF(G3:GG3;"n")+COUNTIF(G3:GG3;"a")))

I am sure you get the idea...

jaclaz

[army20]

THANKS a lot guys !

I'm getting really close to my goal. Last thing I have to know is a bit more complicated.

The formula you gave to me is all about calculating the % of time my soldiers where present during the year. But there is mendatory days and non mendatory days.

So I would like to formula to COUNTIF(G3:GG3;"p") only when the cell pattern is green. Is that possible ???

I'm now entering a PF when it's a non mendatory day but it will be very appreciated if I can use the formula with formating

-Army

[clavicle]

@jaclaz

Yes, that is the solution, I knew it is somewhere, but couldn't figure out how!

[army20]

Anyone have an idea to help me solve my last post (#12) ???

[Anne Troy]

Army:

You need VBA to do that. See Chip Pearson's site:

http://www.cpearson.com/excel/colors.htm

~Navy