How to perform "ifcount" in many sheets

[Khoshravan]

Mistakenly I post this to Excel Setup. This is the same post

I have a column, lets call pipeID (this contains 1500 pipeID) these data are
stored in sheet called "original".
I have run a program for damagae analysis of these pipes, 50 times (for 50
scenarios).
I have stroed result for each run in separate sheet.
I have 50 sheets named:run1, run2, …, run50.
In each sheet I have following data: PipeID of broken pipes and pipeID of
leaked pipes. (In each run only few pipe out of 1500 pipes are broken or
leaked, relativley a very small number of pipes)
Now I want to perform a countif command over these 50 sheets separetly for
leaks and breaks, and write result in "original sheet" in front of each
PipeID for leaks and break separately.?

Solution one) Easiest way
I can perform countif for each sheet separately and write result in
"original" sheet, then I can add these 50 cells.

Better solution)
Question one: I was wondering if it is possible to perform countif command
over 50 sheets at ones.

Question 2) My method is very straight forward and easy but with no talent
on it. I would like to know if there is any smarter way to count number of
cells over more than one sheet?
--
Rasoul Khoshravan Azar
Civil Engineer, Osaka, Japan

[Biff]

Hi!

Not sure this is better but you can try it and see if it affects
performance:

Assume on your 50 sheets column B contains the word "broken" and column C
contains the word "leaked".

To count instances of broken:

=SUMPRODUCT(COUNTIF(INDIRECT("run"&ROW(INDIRECT("1:50"))&"!B:B"),"broken"))

To count instances of leaked:

=SUMPRODUCT(COUNTIF(INDIRECT("run"&ROW(INDIRECT("1:50"))&"!C:C"),"leaked"))

Biff

"Khoshravan" <[email protected]> wrote in message
news:[email protected]...
> Mistakenly I post this to Excel Setup. This is the same post
>
> I have a column, lets call pipeID (this contains 1500 pipeID) these data
> are
> stored in sheet called "original".
> I have run a program for damagae analysis of these pipes, 50 times (for 50
> scenarios).
> I have stroed result for each run in separate sheet.
> I have 50 sheets named:run1, run2, ., run50.
> In each sheet I have following data: PipeID of broken pipes and pipeID of
> leaked pipes. (In each run only few pipe out of 1500 pipes are broken or
> leaked, relativley a very small number of pipes)
> Now I want to perform a countif command over these 50 sheets separetly for
> leaks and breaks, and write result in "original sheet" in front of each
> PipeID for leaks and break separately.?
>
> Solution one) Easiest way
> I can perform countif for each sheet separately and write result in
> "original" sheet, then I can add these 50 cells.
>
> Better solution)
> Question one: I was wondering if it is possible to perform countif command
> over 50 sheets at ones.
>
> Question 2) My method is very straight forward and easy but with no talent
> on it. I would like to know if there is any smarter way to count number of
> cells over more than one sheet?
> --
> Rasoul Khoshravan Azar
> Civil Engineer, Osaka, Japan

[Khoshravan]

Dear Biff
Thanks for your reply.
I know what is the Sumproduct function, but I couldn't understand its role
in your formula. It sounds you are using a very speciall character of
sumproduct.
If possible, please give how does sumproduct works in your formula. What are
your arrays for sunproduct?
--
Rasoul Khoshravan Azar
Civil Engineer, Osaka, Japan


"Biff" wrote:

> Hi!
>
> Not sure this is better but you can try it and see if it affects
> performance:
>
> Assume on your 50 sheets column B contains the word "broken" and column C
> contains the word "leaked".
>
> To count instances of broken:
>
> =SUMPRODUCT(COUNTIF(INDIRECT("run"&ROW(INDIRECT("1:50"))&"!B:B"),"broken"))
>
> To count instances of leaked:
>
> =SUMPRODUCT(COUNTIF(INDIRECT("run"&ROW(INDIRECT("1:50"))&"!C:C"),"leaked"))
>
> Biff
>
> "Khoshravan" <[email protected]> wrote in message
> news:[email protected]...
> > Mistakenly I post this to Excel Setup. This is the same post
> >
> > I have a column, lets call pipeID (this contains 1500 pipeID) these data
> > are
> > stored in sheet called "original".
> > I have run a program for damagae analysis of these pipes, 50 times (for 50
> > scenarios).
> > I have stroed result for each run in separate sheet.
> > I have 50 sheets named:run1, run2, ., run50.
> > In each sheet I have following data: PipeID of broken pipes and pipeID of
> > leaked pipes. (In each run only few pipe out of 1500 pipes are broken or
> > leaked, relativley a very small number of pipes)
> > Now I want to perform a countif command over these 50 sheets separetly for
> > leaks and breaks, and write result in "original sheet" in front of each
> > PipeID for leaks and break separately.?
> >
> > Solution one) Easiest way
> > I can perform countif for each sheet separately and write result in
> > "original" sheet, then I can add these 50 cells.
> >
> > Better solution)
> > Question one: I was wondering if it is possible to perform countif command
> > over 50 sheets at ones.
> >
> > Question 2) My method is very straight forward and easy but with no talent
> > on it. I would like to know if there is any smarter way to count number of
> > cells over more than one sheet?
> > --
> > Rasoul Khoshravan Azar
> > Civil Engineer, Osaka, Japan
>
>
>

[Bob Phillips]

It effectively does a COUNTIF(B:B,"broken") on every sheet. The
"run"&ROW(INDIRECT("1:50")) builds an array of the sheet names that looks
like {"run1";"run2";"run3";"run4";"run5";"run6";etc.}, which is concatenated
with the range to test, &"!B:B"), to build an array of the range to test on
those sheets. The results are passed back to SUMPRODUCT as an array which
sums them.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Khoshravan" <[email protected]> wrote in message
news:[email protected]...
> Dear Biff
> Thanks for your reply.
> I know what is the Sumproduct function, but I couldn't understand its role
> in your formula. It sounds you are using a very speciall character of
> sumproduct.
> If possible, please give how does sumproduct works in your formula. What
are
> your arrays for sunproduct?
> --
> Rasoul Khoshravan Azar
> Civil Engineer, Osaka, Japan
>
>
> "Biff" wrote:
>
> > Hi!
> >
> > Not sure this is better but you can try it and see if it affects
> > performance:
> >
> > Assume on your 50 sheets column B contains the word "broken" and column
C
> > contains the word "leaked".
> >
> > To count instances of broken:
> >
> >
=SUMPRODUCT(COUNTIF(INDIRECT("run"&ROW(INDIRECT("1:50"))&"!B:B"),"broken"))
> >
> > To count instances of leaked:
> >
> >
=SUMPRODUCT(COUNTIF(INDIRECT("run"&ROW(INDIRECT("1:50"))&"!C:C"),"leaked"))
> >
> > Biff
> >
> > "Khoshravan" <[email protected]> wrote in message
> > news:[email protected]...
> > > Mistakenly I post this to Excel Setup. This is the same post
> > >
> > > I have a column, lets call pipeID (this contains 1500 pipeID) these
data
> > > are
> > > stored in sheet called "original".
> > > I have run a program for damagae analysis of these pipes, 50 times
(for 50
> > > scenarios).
> > > I have stroed result for each run in separate sheet.
> > > I have 50 sheets named:run1, run2, ., run50.
> > > In each sheet I have following data: PipeID of broken pipes and pipeID
of
> > > leaked pipes. (In each run only few pipe out of 1500 pipes are broken
or
> > > leaked, relativley a very small number of pipes)
> > > Now I want to perform a countif command over these 50 sheets separetly
for
> > > leaks and breaks, and write result in "original sheet" in front of
each
> > > PipeID for leaks and break separately.?
> > >
> > > Solution one) Easiest way
> > > I can perform countif for each sheet separately and write result in
> > > "original" sheet, then I can add these 50 cells.
> > >
> > > Better solution)
> > > Question one: I was wondering if it is possible to perform countif
command
> > > over 50 sheets at ones.
> > >
> > > Question 2) My method is very straight forward and easy but with no
talent
> > > on it. I would like to know if there is any smarter way to count
number of
> > > cells over more than one sheet?
> > > --
> > > Rasoul Khoshravan Azar
> > > Civil Engineer, Osaka, Japan
> >
> >
> >