COUNT all "P" from a row

I have a presence row in a sheet (Google or MS Excel, does not really matter), having values "A" or "P" for each name.

I need to count how many "P" I have, to make the cell RED if there is any "P" in that row.

 Name 1 2 3 4
______________
 John A A P A
 Mary P P P A
 Suzy P P P P
 Dick A A A A

So I need red cell to Dick...

How to count the "P" symbols from a given row?

PS. Some restrictions:

I need the whole line starting with, say the second column "to the end", like B2:B?
Is there a way to have an universal formula? say =COUNTIF(B2:B, "P") > 0 to became =COUNTIF(B+ROW():B, "P") > 0

Serge

Posted 2016-02-01T12:02:54.973

Reputation: 406

The row Dick is the only one without a P, but it sounds like you want that one to be red? Can you confirm? Have you attempted any conditional formatting to achieve this? – Jonno – 2016-02-01T12:08:30.700

conditional formatting – Raystafarian – 2016-02-01T12:10:49.113

Answers

Try the following conditional formatting:

=COUNTIF($B2:$E2,"P")>0

Applying to:

=$B$2:$E$5

Jonno

Posted 2016-02-01T12:02:54.973

Reputation: 18 756

You can do this in Google Spreadsheets:

Name    Hidden  1   2   3   4
John    1       A   A   P   A
Mary    3       P   P   P   A
Suzy    4       P   P   P   P
Dick    0       A   A   A   A

You set the dick name column via Format - Conditional Formatting - Custom formula is =B5=0 e.g., then you copy the first Dick column to other rows and change the names or set it the same way for other rows. Then, you can hide the Hidden column. In Excel there is conditional formatting as well, can't remember where exactly, you can also Ctrl+Alt+C (or V just formatting/text), don't know if this is possible in Google Spreadsheets.

EDIT: the hidden column content is =COUNTIF(C5:F5,"P") for Dick, similarly for other rows.

13ushm4n

Posted 2016-02-01T12:02:54.973

Reputation: 101