COUNTIF FUNCTION

[Jeanne]

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

 

[Sheeloo]

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

 

[Ashish Mathur]

Hi,

You can use this formula

SUMPRODUCT(COUNTIF(INDIRECT("Sheet"&H9:H11&"!C18:C28"),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

 

[T. Valko]

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

Summary sheet A1 = your text criteria

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

 

[Jeanne]

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

 

[Jeanne]

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...

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

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