Count Text within Multiple Worksheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a workbook with several sheets. I am trying to determine how many
times "x" appears - using a specific cell (A5) as my reference in each of
the sheets. I want to count how many times "x" appears in cell A5 within all
the worksheets and summarize on a summary worksheet. So, if I have 9
worksheets that I want to count, I want to know that "x" appears in only 7 of
the worksheets.
 
Try this UDF:


Function laffin()
Application.Volatile
laffin = 0
For i = 1 To Sheets.Count
If Sheets(i).Name = "summary" Then
Else
If Sheets(i).Range("A5").Value = "x" Then
laffin = laffin + 1
End If
End If
Next
End Function
 
Are the sheets named like name1. name2 and so on? If so use

=SUMPRODUCT(COUNTIF(INDIRECT("'Name"&{1;2;3;4;5;6;7;8;9}&"'!A5"),"x"))

replace Name with whatever name you use for the sheets

however if each name is not in a patter like the above the easiest way is
to put all sheets name in a range, for instance if you put then in H1:H9 in
the summary sheet,

then you can use

=SUMPRODUCT(COUNTIF(INDIRECT("'"&H1:H9&"'!A5"),"x"))


or select H1:H9, do insert>name>define and give it a name like
MySheets, then you can use


=SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!A5"),"x"))
 
=SUM(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT("1:9"))&"!A5"),"x"))

ctrl+shift+enter, not just enter
 
Hi

One way would be to use another cell on each sheet, e.g. cell X1 with a
formula
=IF(A5="x",1,0)

then on your Summary sheet (presumably Sheet10)
=SUM(Sheet1:Sheet9!X1)
You could insert 2 dummy sheets, and name them First and Last.
Drag them to positions where they encompass your 9 sheets in a sort of
"sandwich", with your summary sheet being outside of the sandwich.
Then
=SUM(First:Last!X1)

You can then drag sheets in and out of the sandwich to make the count
only operational on some of your sheets if you wish.
 
Your recommendation of putting the sheet names in a range of cells worked for
me. Thank you so much for that. However, since most of my sheets will not
likely have a pattern, if I only had two sheets I wanted to use and the names
were 43-03951804 and 80-06069282, how would I write the formula without
putting them in a range?
 
If you only have 2 sheets you can hardcode the name into the formula like
this

=SUMPRODUCT(COUNTIF(INDIRECT("'"&{"43-03951804";"80-06069282"}&"'!A5"),"x"))


obviously if you had 30 sheets the formula would get out of hand hardcoded
 
Perfect! Thank you so much!

Peo Sjoblom said:
If you only have 2 sheets you can hardcode the name into the formula like
this

=SUMPRODUCT(COUNTIF(INDIRECT("'"&{"43-03951804";"80-06069282"}&"'!A5"),"x"))


obviously if you had 30 sheets the formula would get out of hand hardcoded
Click to expand...
 
Gary''s Student said:
Try this UDF:

Function laffin()
Click to expand...
....

No arguments - usually a bad sign.
For i = 1 To Sheets.Count
If Sheets(i).Name = "summary" Then
Click to expand...

Next bad sign - using hardcoded worksheet names.
Else
If Sheets(i).Range("A5").Value = "x" Then
Click to expand...
....

Next bad sign - using hardcoded cell references.

There are better ways to write such a udf, and there are already
several dozen in the newsgroup archive, so I won't add another.

But udfs are slow. How about an .XLL add-in? Specifically, Laurent
Longre's MOREFUNC.XLL add-in, available at

http://xcell05.free.fr/english/

Once it's installed try

=SUMPRODUCT(--(THREED(alpha:omega!A5)="x"))
 
If you only have 2 sheets involved:

=COUNTIF('43-03951804'!A5,"x")+COUNTIF('80-06069282'!A5,"x")

Biff
 
If I wanted to insert two dummy sheets at the first and last of my worksheets
(called First and Last). I tried the following formula:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&First:Last&"'!F16"),"x"))

and got this error (#NAME?). I would like to be able to just insert sheets
like this if possible as I will be receiving the worksheets via email and
just need to summarize specific fields.
 
Back
Top