Is it possible to have a 3D sumif result?
 

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é

Is it possible to have a 3D sumif result?
 

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é

Is it possible to have a 3D sumif result?
 

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é

Is it possible to have a 3D sumif result?
 

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é

Is it possible to have a 3D sumif result?
 

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é