If Statements help

[Haris75]

I know how to use very basic If statements, but im stuck here :

I have a spread sheet where i record 3 outcomes, either "y" (yes) "n" (no) or "l" (late). There are 5 of these a day, I want an if statement to add all of the y's and l's together. For example if one day i had 3 y, 1 L and one N i want the cell with the if statement to show 4 (3y+1L)

Any help would be appreciated. If you need more explanation let me know, the cells with the y's and n's are B5:F5, outcome cell is G5

What I have got right now is :

=IF(B5="y",(1),IF(B5="l",(1),(0)))

But I have to do this in 5 different cells, anyway i can combine it to do it all in one?

[JBeaucaire]

Try this:

=COUNTIF($B$5:$F$5,"y") + COUNTIF($B$5:$F$5,"l")

It's not case-sensitive, either.

[DonkeyOte]

Or possibly

=COUNTIF($B$5:$F$5,"<>n")

[Haris75]

Wow thanks to both of you! I had no idea what the COUNTIF was for haha, it looks a lot easier than nesting IF functions.

And DonkeyOte, can you tell me what the "<>n" is for in =COUNTIF($B$5:$F$5,"<>n") ?

Edit : nvm i think its saying "anything other than n", right? What if I wanted anything other than "n" as well as blank cells?

[JBeaucaire]

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

[Haris75]

Sorry it wasnt, as I said last post

What if I wanted anything other than "n" as well as blank cells

in the formula =COUNTIF($B$5:$F$5,"<>n")

[JBeaucaire]

What if I wanted anything other than "n" as well as blank cells

This seems to completely differ from your original post which presented the need to add all the cells in a 5-cell range with a "y" or "l" answer.

But the answer then goes similarly:
=5-COUNTIF($B5:$F5,"<>n")
...or my original idea, necessary if there is anything other than, y/n/l/blank
=COUNTIF($B5:$F5,"y") + COUNTIF($B5:$F5,"l") + COUNTIF($B5:$F5,"")

I've also adjusted the $ symbols so you can copy this formula downward for other rows.

[DonkeyOte]

Where the tests are constant you can also convert

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

saves on typing, nothing more.

[JBeaucaire]

Please Login or Register  to view this content.

saves on typing, nothing more.

Well, there's the neato-factor, too. Some formulas just "look" better, heh. Very nice.