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
--
Gary''s Student - gsnu200715

Gary''s Student wrote...
Try this UDF:

Function laffin()
....

No arguments - usually a bad sign.

For i = 1 To Sheets.Count
If Sheets(i).Name = "summary" Then

Next bad sign - using hardcoded worksheet names.

Else
If Sheets(i).Range("A5").Value = "x" Then
....

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

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

ctrl+shift+enter, not just enter


"Laffin" wrote:

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.

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 insertnamedefine and give it a name like
MySheets, then you can use


=SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!A5"), "x"))


--
Regards,

Peo Sjoblom



"Laffin" wrote in message
...
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.

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?

"Peo Sjoblom" wrote:

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 insertnamedefine and give it a name like
MySheets, then you can use


=SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!A5"), "x"))


--
Regards,

Peo Sjoblom



"Laffin" wrote in message
...
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.

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


--
Regards,

Peo Sjoblom



"Laffin" wrote in message
...
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?

"Peo Sjoblom" wrote:

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 insertnamedefine and give it a name like
MySheets, then you can use


=SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!A5"), "x"))


--
Regards,

Peo Sjoblom



"Laffin" wrote in message
...
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.

Perfect! Thank you so much!

"Peo Sjoblom" wrote:

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


--
Regards,

Peo Sjoblom



"Laffin" wrote in message
...
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?

"Peo Sjoblom" wrote:

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 insertnamedefine and give it a name like
MySheets, then you can use


=SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!A5"), "x"))


--
Regards,

Peo Sjoblom



"Laffin" wrote in message
...
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.

If you only have 2 sheets involved:

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

Biff

"Laffin" wrote in message
...
Perfect! Thank you so much!

"Peo Sjoblom" wrote:

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


--
Regards,

Peo Sjoblom



"Laffin" wrote in message
...
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?

"Peo Sjoblom" wrote:

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 insertnamedefine and give it a name like
MySheets, then you can use


=SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!A5"), "x"))


--
Regards,

Peo Sjoblom



"Laffin" wrote in message
...
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.

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.


"Peo Sjoblom" wrote:

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


--
Regards,

Peo Sjoblom



"Laffin" wrote in message
...
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?

"Peo Sjoblom" wrote:

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 insertnamedefine and give it a name like
MySheets, then you can use


=SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!A5"), "x"))


--
Regards,

Peo Sjoblom



"Laffin" wrote in message
...
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.

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.


--
Regards

Roger Govier


"Laffin" wrote in message
...
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.