Hello,

I tried to do a 3D sumif formula, but it didn't work.
=sumif(sheet1:sheet6!a1,"0") but I got a #VALUE


Does anyone have a method that can be used to get the result that I want?
And could the solution be used for the "Countif" and "Average" functions as
well?

Thanks in advance

André

Hi!

Create a list of your sheet names and give that list a defined name. Say
that name is Snames.

Then:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Snames&"'!A1"),"0" ))

Biff

"Andre Croteau" wrote in message
...
Hello,

I tried to do a 3D sumif formula, but it didn't work.
=sumif(sheet1:sheet6!a1,"0") but I got a #VALUE


Does anyone have a method that can be used to get the result that I want?
And could the solution be used for the "Countif" and "Average" functions
as
well?

Thanks in advance

André

Ooops!

And could the solution be used for the "Countif" and "Average" functions as
well?

For Countif:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Snames&"'!A1")," 0"))

For Average:

=AVERAGE(Sheet1:Sheet3!A1)

If you want a conditional average: 0

Then just use the Sumproduct(Sumif.........)/Sumproduct(Countif(........)

Biff

"Biff" wrote in message
...
Hi!

Create a list of your sheet names and give that list a defined name. Say
that name is Snames.

Then:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Snames&"'!A1"),"0" ))

Biff

"Andre Croteau" wrote in message
...
Hello,

I tried to do a 3D sumif formula, but it didn't work.
=sumif(sheet1:sheet6!a1,"0") but I got a #VALUE


Does anyone have a method that can be used to get the result that I want?
And could the solution be used for the "Countif" and "Average" functions
as
well?

Thanks in advance

André

Hi again Biff,

Fantastic stuff!! Thanks

André

"Biff" wrote in message
...
Ooops!

And could the solution be used for the "Countif" and "Average" functions
as
well?

For Countif:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Snames&"'!A1")," 0"))

For Average:

=AVERAGE(Sheet1:Sheet3!A1)

If you want a conditional average: 0

Then just use the Sumproduct(Sumif.........)/Sumproduct(Countif(........)

Biff

"Biff" wrote in message
...
Hi!

Create a list of your sheet names and give that list a defined name. Say
that name is Snames.

Then:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Snames&"'!A1"),"0" ))

Biff

"Andre Croteau" wrote in message
...
Hello,

I tried to do a 3D sumif formula, but it didn't work.
=sumif(sheet1:sheet6!a1,"0") but I got a #VALUE


Does anyone have a method that can be used to get the result that I
want?
And could the solution be used for the "Countif" and "Average"
functions
as
well?

Thanks in advance

André

You're welcome. Thanks for the feedback!

Biff

"Andre Croteau" wrote in message
...
Hi again Biff,

Fantastic stuff!! Thanks

André

"Biff" wrote in message
...
Ooops!

And could the solution be used for the "Countif" and "Average" functions
as
well?

For Countif:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Snames&"'!A1")," 0"))

For Average:

=AVERAGE(Sheet1:Sheet3!A1)

If you want a conditional average: 0

Then just use the Sumproduct(Sumif.........)/Sumproduct(Countif(........)

Biff

"Biff" wrote in message
...
Hi!

Create a list of your sheet names and give that list a defined name.
Say
that name is Snames.

Then:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Snames&"'!A1"),"0" ))

Biff

"Andre Croteau" wrote in message
...
Hello,

I tried to do a 3D sumif formula, but it didn't work.
=sumif(sheet1:sheet6!a1,"0") but I got a #VALUE


Does anyone have a method that can be used to get the result that I
want?
And could the solution be used for the "Countif" and "Average"
functions
as
well?

Thanks in advance

André