Is it possible to have a 3D sumif result?

A

Andre Croteau

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é
 
B

Biff

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
 
B

Biff

Ooops!
And could the solution be used for the "Countif" and "Average" functions as
well?
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using a SUMIF and Parsing to sum part of a cell 6
Is it possible to have a function like Sumif but for averages 1
SUMIF using #N/A error as criteria 3
Where to put quotes in a SUMIF formula and when is & used for cell 2
SUMIF, Is this the only way 1
Is a 3D SUMIF formula possible 1
Criteria Syntax in SUMIF formula 4
Excel Need Countifs Formula Help 0

Top