COUNTIF FUNCTION
 

I have a workbook with 31 sheets (for the days of the month). I need to
create a summary count of how many times a certain "text" appears in cells
C65:C75 on the sheets of that workbook. The sheets are just named with the
numbers 1-31. I know that Countif does not work across multiple sheets and I
have tried arrays and nothing works. Please help :(

COUNTIF FUNCTION
 

You need to find a workaround...

Here are a couple;

1.
a. On each sheet in the same cell, say A1 have your formula which counts the
"text"
=COUNTIF(C65:C75,"TEXT")
b. Then in a cell in the summary sheet use
=SUM('1:31'!A1)

or 2.
In the summary sheet enter the numbers 1-31 in A1-A31
now in B1 use this formula
=COUNTIF(INDIRECT("'"&A1&"'!C65:C75"),"TEXT")
and copy down
then use =SUM(B1:B31) to get what you want


"Jeanne" wrote:

I have a workbook with 31 sheets (for the days of the month). I need to
create a summary count of how many times a certain "text" appears in cells
C65:C75 on the sheets of that workbook. The sheets are just named with the
numbers 1-31. I know that Countif does not work across multiple sheets and I
have tried arrays and nothing works. Please help :(

COUNTIF FUNCTION
 

Hi,

You can use this formula

SUMPRODUCT(COUNTIF(INDIRECT("Sheet"&H9:H11&"!C18:C 28"),B18))

In range H9:H11, I have 1,2,3. Please extend this range to include 1-31.
Also, I have assume that the range in which I have to count is C18:C28.
Please adjust the range

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Jeanne" wrote in message
...
I have a workbook with 31 sheets (for the days of the month). I need to
create a summary count of how many times a certain "text" appears in cells
C65:C75 on the sheets of that workbook. The sheets are just named with
the
numbers 1-31. I know that Countif does not work across multiple sheets
and I
have tried arrays and nothing works. Please help :(

COUNTIF FUNCTION
 

With your sheets being named 1, 2, 3, .....31

Summary sheet A1 = your text criteria

=SUMPRODUCT(COUNTIF(INDIRECT("'"&ROW(INDIRECT("1:3 1"))&"'!C65:C75"),A1))

--
Biff
Microsoft Excel MVP


"Jeanne" wrote in message
...
I have a workbook with 31 sheets (for the days of the month). I need to
create a summary count of how many times a certain "text" appears in cells
C65:C75 on the sheets of that workbook. The sheets are just named with
the
numbers 1-31. I know that Countif does not work across multiple sheets
and I
have tried arrays and nothing works. Please help :(

COUNTIF FUNCTION
 

Thank you for your reply...I will give it a try when I work on it again!



"T. Valko" wrote:

With your sheets being named 1, 2, 3, .....31

Summary sheet A1 = your text criteria

=SUMPRODUCT(COUNTIF(INDIRECT("'"&ROW(INDIRECT("1:3 1"))&"'!C65:C75"),A1))

--
Biff
Microsoft Excel MVP


"Jeanne" wrote in message
...
I have a workbook with 31 sheets (for the days of the month). I need to
create a summary count of how many times a certain "text" appears in cells
C65:C75 on the sheets of that workbook. The sheets are just named with
the
numbers 1-31. I know that Countif does not work across multiple sheets
and I
have tried arrays and nothing works. Please help :(

COUNTIF FUNCTION
 

Thanks for your help...I did try something similiar and I did not get the
result I expected but, I am going to go back and try again...:)

"Sheeloo" wrote:

You need to find a workaround...

Here are a couple;

1.
a. On each sheet in the same cell, say A1 have your formula which counts the
"text"
=COUNTIF(C65:C75,"TEXT")
b. Then in a cell in the summary sheet use
=SUM('1:31'!A1)

or 2.
In the summary sheet enter the numbers 1-31 in A1-A31
now in B1 use this formula
=COUNTIF(INDIRECT("'"&A1&"'!C65:C75"),"TEXT")
and copy down
then use =SUM(B1:B31) to get what you want


"Jeanne" wrote:

I have a workbook with 31 sheets (for the days of the month). I need to
create a summary count of how many times a certain "text" appears in cells
C65:C75 on the sheets of that workbook. The sheets are just named with the
numbers 1-31. I know that Countif does not work across multiple sheets and I
have tried arrays and nothing works. Please help :(

COUNTIF FUNCTION
 

You're welcome!

--
Biff
Microsoft Excel MVP


"Jeanne" wrote in message
...
Thank you for your reply...I will give it a try when I work on it again!



"T. Valko" wrote:

With your sheets being named 1, 2, 3, .....31

Summary sheet A1 = your text criteria

=SUMPRODUCT(COUNTIF(INDIRECT("'"&ROW(INDIRECT("1:3 1"))&"'!C65:C75"),A1))

--
Biff
Microsoft Excel MVP


"Jeanne" wrote in message
...
I have a workbook with 31 sheets (for the days of the month). I need to
create a summary count of how many times a certain "text" appears in
cells
C65:C75 on the sheets of that workbook. The sheets are just named
with
the
numbers 1-31. I know that Countif does not work across multiple sheets
and I
have tried arrays and nothing works. Please help :(