I want to know how often a name (say jack) occurs in a spreadsheet.
The name has to be found in the range c6:f40 but.........
i have 50 sheets. named 1, 2, 3, 4, .......50
and i want to know the sum of how often a name occurs in all sheets
together.

Can someone help me?
Greetings Hans

Hello Hans

The formula you need for this is countif. Where you require the answer, type
=countif(
to select all of your 50 sheets, hold down shift as you select the first and
last tab, so the formula will read =countif('Sheet1:Sheet50'
then select the range c6:f40
=countif('Sheet1:Sheet50'!c6:f40
the last bit says what you're counting, so "jack". Because this is text,
not a number you need to enclose it in quotes, so your entire formula should
read

=countif('Sheet1:Sheet50'!c6:f40;"jack")

don't forget the ; between the range and what you're looking for

hope this helps

Liz

"hans" wrote:

I want to know how often a name (say jack) occurs in a spreadsheet.
The name has to be found in the range c6:f40 but.........
i have 50 sheets. named 1, 2, 3, 4, .......50
and i want to know the sum of how often a name occurs in all sheets
together.

Can someone help me?
Greetings Hans

Apologies Hans, an error in my last post, the range should be split from the
criteria with a comma not a semi-colon. I've also just tried with a range,
and it doesn't seem to work, so you might need to do this function on each
sheet, and then sum them at the end.

"hans" wrote:

I want to know how often a name (say jack) occurs in a spreadsheet.
The name has to be found in the range c6:f40 but.........
i have 50 sheets. named 1, 2, 3, 4, .......50
and i want to know the sum of how often a name occurs in all sheets
together.

Can someone help me?
Greetings Hans

If in fact your sheets are named 1, 2, 3, etc. and not Sheet1, Sheet2,
Sheet3, etc., try...

=SUMPRODUCT(COUNTIF(INDIRECT(ROW(INDIRECT("1:50")) &"!C6:F40"),"jack"))

....otherwise, try...

=SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&ROW(INDIRECT ("1:50"))&"'!C6:F40"),"
jack"))

Hope this helps!

In article ,
"hans" wrote:

I want to know how often a name (say jack) occurs in a spreadsheet.
The name has to be found in the range c6:f40 but.........
i have 50 sheets. named 1, 2, 3, 4, .......50
and i want to know the sum of how often a name occurs in all sheets
together.

Can someone help me?
Greetings Hans

thanks, this is exactly what i needed.

Greetings Hans


"Domenic" schreef in bericht
...
If in fact your sheets are named 1, 2, 3, etc. and not Sheet1, Sheet2,
Sheet3, etc., try...

=SUMPRODUCT(COUNTIF(INDIRECT(ROW(INDIRECT("1:50")) &"!C6:F40"),"jack"))

...otherwise, try...

=SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&ROW(INDIRECT ("1:50"))&"'!C6:F40"),"
jack"))

Hope this helps!

In article ,
"hans" wrote:

I want to know how often a name (say jack) occurs in a spreadsheet.
The name has to be found in the range c6:f40 but.........
i have 50 sheets. named 1, 2, 3, 4, .......50
and i want to know the sum of how often a name occurs in all sheets
together.

Can someone help me?
Greetings Hans