Multisheet range in SUMPRODUCT?

J

Joe User

When I write SUM(Sheet1:Sheet4!B4:B6), that works.

But if I change the function name to SUMPRODUCT, I get a #REF error. Copied
from the Formula Bar:

=SUMPRODUCT(Sheet1:Sheet4!B4:B6)

And if I use that form of range in a subexpression, I get a #NAME error.
Copied from the Formula Bar:

=SUMPRODUCT(--(sheet1:Sheet4!B4:B6<=20))

Note that Sheet1 is lowercased by Excel. All my changes were made by
successive minimum editing of the original SUM formula.

Is there some way to make this kind of range work with SUMPRODUCT, short of
the obvious, namely breaking this into multiple subexpressions?
 
B

Bob Phillips

Try

=SUMPRODUCT(--(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT("1:4"))&"!B4:B6"),"<=20")))
 
J

Joe User

Bob Phillips said:
=SUMPRODUCT(--(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT("1:4"))&"!B4:B6"),"<=20")))

Thanks. That is helpful. However, that requires that Sheet3 exists, for
example. A range of the form Sheet1:Sheet4!B4:B6 works (with SUM) even if
there is no Sheet3.


----- original message -----
 
J

Joe User

I said:
Bob Phillips said:
=SUMPRODUCT(--(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT("1:4"))&"!B4:B6"),"<=20")))
[....]
However, that requires that Sheet3 exists, for example.

I was able to leverage the idea to produce the following, albeit not as
robust as I would like:

=SUMPRODUCT(--(COUNTIF(INDIRECT({"sheet1","sheet2","sheet4"}&"!B4:B6"),"<=20")))

However, I really want to get COUNTIF out of there. The formula I would
like is something like:

=SUMPRODUCT(--(INDIRECT({"sheet1","sheet2","sheet4"}&"!a4:a6")=A5),
INDIRECT({"sheet1","sheet2","sheet4"}&"!b4:b6"))

Again, the obvious workaround is:

=SUMIF(Sheet1!A4:A6,A5,Sheet1!B4:B6)
+SUMIF(Sheet2!A4:A6,A5,Sheet2!B4:B6)
+SUMIF(Sheet4!A4:A6,A5,Sheet4!B4:B6)

And I guess that's not bad, if I must enumerate the worksheet names in the
INDIRECT expression.

I was hoping for something tidier and more extensible.


----- original message -----
 
T

T. Valko

For counting frequencies of numbers in 3D ranges:

=INDEX(FREQUENCY(Sheet1:Sheet4!B4:B6,20),1)

=INDEX(FREQUENCY(Sheet1:Sheet4!B4:D6,20),1)

You can even use multiple area references (but not across sheets):

=INDEX(FREQUENCY((B4:B6,D4:D6,X1:X100),20),1)
 
T

T. Valko

that requires that Sheet3 exists

This doesn't:

=INDEX(FREQUENCY(Sheet1:Sheet4!B4:B6,20),1)

--
Biff
Microsoft Excel MVP


Joe User said:
I said:
Bob Phillips said:
=SUMPRODUCT(--(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT("1:4"))&"!B4:B6"),"<=20")))
[....]
However, that requires that Sheet3 exists, for example.

I was able to leverage the idea to produce the following, albeit not as
robust as I would like:

=SUMPRODUCT(--(COUNTIF(INDIRECT({"sheet1","sheet2","sheet4"}&"!B4:B6"),"<=20")))

However, I really want to get COUNTIF out of there. The formula I would
like is something like:

=SUMPRODUCT(--(INDIRECT({"sheet1","sheet2","sheet4"}&"!a4:a6")=A5),
INDIRECT({"sheet1","sheet2","sheet4"}&"!b4:b6"))

Again, the obvious workaround is:

=SUMIF(Sheet1!A4:A6,A5,Sheet1!B4:B6)
+SUMIF(Sheet2!A4:A6,A5,Sheet2!B4:B6)
+SUMIF(Sheet4!A4:A6,A5,Sheet4!B4:B6)

And I guess that's not bad, if I must enumerate the worksheet names in the
INDIRECT expression.

I was hoping for something tidier and more extensible.


----- original message -----

Joe User said:
Thanks. That is helpful. However, that requires that Sheet3 exists, for
example. A range of the form Sheet1:Sheet4!B4:B6 works (with SUM) even
if there is no Sheet3.


----- original message -----
 

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

Top