Excel help please

[camioneur]

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

[Hammer]

If the names are all in column A, try

=countif(A:A,"Pete")

[camioneur]

No they're in 12 different columns (1 for each month)

[Bushwhacker]

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

[Hammond Egg]

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!

[Bushwhacker]

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")

[camioneur]

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