Donate SIGN UP

Excel help please

Avatar Image
camioneur | 11:24 Fri 14th Mar 2008 | Technology
6 Answers
I want a cell to automatically give me the total of how many cells in a worksheet contain a certain text. Eg, A holiday sheet with a guy's name in 6 places, say "Pete". I want a cell somewhere on the same sheet to tell me that he's already had 6 days and to update automatically when I put his name in the sheet again somewhere else when he books another day off or a week etc. TIA
Gravatar

Answers

1 to 6 of 6rss feed

Best Answer

No best answer has yet been selected by camioneur. Once a best answer has been selected, it will be shown here.

For more on marking an answer as the "Best Answer", please visit our FAQ.
If the names are all in column A, try

=countif(A:A,"Pete")
Question Author
No they're in 12 different columns (1 for each month)
You could use the COUNTIF function.

You have to specify the data range and the value that you are looking for.

Lets say that your data range is from cell A1 to cell D20 and you were looking for a count of the value Pete.

In a blank cell (outwith your data range) insert function :

=COUNTIF($A$1:$D$20, "Pete")

This formula will return the number of times Pete appears in your data range.

BW
If Pete's name is going to appear(say) in column A, anywhere in rows 1-100, enter the formula somewhere outside a1-a100:

=COUNTIF(a1:a100,"Pete")

Don't forget the comma after the cell range!
Well I guess if you have 12 month columns starting from column A, then the formula you will need to fine Pete is :

=COUNTIF($A$1:$L$31, "Pete")



Question Author
Thanks all, brilliant..sorted..easy when you know how but difficult when you don't..cheers

1 to 6 of 6rss feed

Do you know the answer?

Excel help please

Answer Question >>