Need a formula to count cells with certain text. 2

[Guest_imported]

I'm working on a personnel spreadsheet. When there's a gain it is shown by +Thompson. If there's a loss, it is shown by -Thompson. Is there a way to take a beginning number and subtract or add the + or - ?

 

[acron]

Use the LEFT() function, syntax :

LEFT(String Expression, Number of Characters)

e.g. =LEFT("+Thompson",1), returns "+". You can use =LEFT(A1,1) if the text is in A1.

If you want to get the string without the leadeing character use a combination of RIGHT() and LEN()

=RIGHT(A1,LEN(A1)-1), would return "Thompson" if A1 contained "XThompson", where X is any character.

Hope that helps

 

[Guest_imported]

I think I worded this wrong. The spreadsheet shows the text in the cell, if it's a gain, as "+Name". If there's a loss, it is shown as "-Name". There is a starting number of personnel ... if the cell shows "-", I want to subtract 1 from the starting number. If the cell shows "+", I want to add 1 to the starting number. Is there a formula for this? I need to keep a total of personnel in a department, showing gains and losses. Thanks!

 

[AlaskanDad]

Here is the formula by which your staff changes (+ or -):

=COUNTIF($A$1:$A$1000,"+*&quot-COUNTIF($A$1:$A$1000,"-*&quot

Assumptions:
All of your adds and drops are listed in column A
You have 1000 or less of these (add a couple zeros if you need to)

If you have all of your staff in there from the time they enter the company until the time they leave, this formula will give you your total company personnel.

Enjoy!

 

[Guest_imported]

Thanks!!!! That's just what I was looking for ... it worked GREAT!! Thanks again!

 

[DeLaMartre]

Brilliant! I love elegant solutions such as the one supplied by AlaskanDad! He gets my star, also!

Bob in California