3d References

[AFD at FONO]

If I have a number of worksheets between a worksheet called FIRST and a
worksheet called LAST in a workbook I can sum the contents of cells, say A1,
on all these worksheets with =SUM(FIRST:LAST!A1). The sum will, of course,
include the contents of FIRST!A1 and LAST!A1. If, however, there is some
condition that determines which cells to include in the sum, say the contents
of cell B1=1, on each worksheet I can't use
=SUMIF(FIRST:LAST!B1,"=1",FIRST:LAST!A1). I get a #VALUE! error.

Question 1. Why can't I use a 3d reference with SUMIF?
Question 2. Any ideas about how to solve this problem?

 

[amontes]

If I have a number of worksheets between a worksheet called FIRST and a
worksheet called LAST in a workbook I can sum the contents of cells, say A1,
on all these worksheets with =SUM(FIRST:LAST!A1).  The sum will, of course,
include the contents of FIRST!A1 and LAST!A1.  If, however, there is some
condition that determines which cells to include in the sum, say the contents
of cell B1=1, on each worksheet  I can't use
=SUMIF(FIRST:LAST!B1,"=1",FIRST:LAST!A1).  I get a #VALUE! error.

Question 1.  Why can't I use a 3d reference with SUMIF?
Question 2.  Any ideas about how to solve this problem?

Check this option: http://www.ozgrid.com/forum/showthread.php?t=27306

 

[Ron Rosenfeld]

If I have a number of worksheets between a worksheet called FIRST and a
worksheet called LAST in a workbook I can sum the contents of cells, say A1,
on all these worksheets with =SUM(FIRST:LAST!A1). The sum will, of course,
include the contents of FIRST!A1 and LAST!A1. If, however, there is some
condition that determines which cells to include in the sum, say the contents
of cell B1=1, on each worksheet I can't use
=SUMIF(FIRST:LAST!B1,"=1",FIRST:LAST!A1). I get a #VALUE! error.

Question 1. Why can't I use a 3d reference with SUMIF?

That is a MS design decision, so you'd have to ask them.

Question 2. Any ideas about how to solve this problem?

Download Longre's free morefunc.xll add-in from
http://xcell05.free.fr/morefunc/english/index.htm

Then use the THREED() function to create an array that you can use non-3d and
array functions with.

I don't think you can use SUMIF, but you could use SUMPRODUCT:

=SUMPRODUCT((THREED(FIRST:LAST!B1)=1)*THREED(FIRST:LAST!A1))

There is a limit of 65,536 cells using this method. So if that is not enough,
you will need a different solution.
--ron

 

[Harlan Grove]

That is a MS design decision, so you'd have to ask them.

More specifically, SUMIF and COUNTIF require range references as 1st
arguments. No obvious reason why that's mandatory, but I suspect SUMIF/
COUNTIF make use of the same code as Edit > Find, which works on
ranges, and the code to do so may be too opaque to try to change.
Anyway, in Excel 3D references aren't range references.

Download Longre's free morefunc.xll add-in fromhttp://xcell05.free.fr/morefunc/english/index.htm

Then use the THREED() function to create an array that you can use non-3d and
array functions with.

I don't think you can use SUMIF, but you could use SUMPRODUCT:

....

Definitely can't use THREED as 1st arguments to SUMIF/COUNTIF. THREED
returns arrays, which aren't range references.

 

[Ron Rosenfeld]

More specifically, SUMIF and COUNTIF require range references as 1st
arguments. No obvious reason why that's mandatory, but I suspect SUMIF/
COUNTIF make use of the same code as Edit > Find, which works on
ranges, and the code to do so may be too opaque to try to change.
Anyway, in Excel 3D references aren't range references.

...

Definitely can't use THREED as 1st arguments to SUMIF/COUNTIF. THREED
returns arrays, which aren't range references.

Thanks for that more detailed explanation.

Certainly, he could use SUMPRODUCT, though.

=sumproduct((threed(first:last!b1=1)*threed(first:last!a1))

-------------------------

Of interest, I note in HELP for the version of morefunc that I have (?3.06)
that there is a countif.3d function, but it does not appear in the
insert/function dialog box.

Also, although Longre's web site appears to be functioning again, after months
of being unable to access it, I cannot read (or post) any messages to the forum
(I receive an error message from the server).

Do you have any knowledge as to what is going on?

--ron

 

[Spiky]

Thanks for that more detailed explanation.

Certainly, he could use SUMPRODUCT, though.

=sumproduct((threed(first:last!b1=1)*threed(first:last!a1))

-------------------------

Of interest, I note in HELP for the version of morefunc that I have (?3.06)
that there is a countif.3d function, but it does not appear in the
insert/function dialog box.

Also, although Longre's web site appears to be functioning again, after months
of being unable to access it, I cannot read (or post) any messages to the forum
(I receive an error message from the server).

Do you have any knowledge as to what is going on?

--ron

I have no knowledge of the website doings, but I did download morefunc
a few months ago and it claims to be v5.06. And countif.3d is right in
the dialog for me. Maybe just update yours. I have noticed that
download.com and some other places have it, even when the home site is
down.

 

[Ron Rosenfeld]

I have no knowledge of the website doings, but I did download morefunc
a few months ago and it claims to be v5.06. And countif.3d is right in
the dialog for me. Maybe just update yours. I have noticed that
download.com and some other places have it, even when the home site is
down.

I'll try downloading it again.

But I had done so from the web site initially; I redownloaded it yesterday, but
did not install it. However, it was the same size as the original.
--ron

 

[mhoffmeier]

The link you gave worked like a champ. I used
=
SUMPRODUCT(SUMIF(INDIRECT("'"&$U$8:$U$10&"'!a1"),D186*1000+E186,INDIRECT("'"&$U$8:$U$10&"'!a33")))
and it returned the matching result on the needed sheet, cell e33.

I went to evaluate the formula to understand how it works, and it returns
the Value! error in the middle of the evaluation, but the correct result in
the end!

How does it work?