Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multisheet range in SUMPRODUCT?
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multisheet range in SUMPRODUCT?
Try
=SUMPRODUCT(--(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT("1:4"))&"!B 4:B6"),"<=20"))) -- HTH Bob "Joe User" <joeu2004 wrote in message ... 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multisheet range in SUMPRODUCT?
"Bob Phillips" wrote:
=SUMPRODUCT(--(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT("1:4"))&"!B 4: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 ----- "Bob Phillips" wrote in message ... Try =SUMPRODUCT(--(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT("1:4"))&"!B 4:B6"),"<=20"))) -- HTH Bob "Joe User" <joeu2004 wrote in message ... 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multisheet range in SUMPRODUCT?
I wrote:
"Bob Phillips" wrote: =SUMPRODUCT(--(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT("1:4"))&"!B 4: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"}&"!B 4: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")=A 5), 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" <joeu2004 wrote in message ... "Bob Phillips" wrote: =SUMPRODUCT(--(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT("1:4"))&"!B 4: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 ----- "Bob Phillips" wrote in message ... Try =SUMPRODUCT(--(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT("1:4"))&"!B 4:B6"),"<=20"))) -- HTH Bob "Joe User" <joeu2004 wrote in message ... 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multisheet range in SUMPRODUCT?
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) -- Biff Microsoft Excel MVP "Bob Phillips" wrote in message ... Try =SUMPRODUCT(--(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT("1:4"))&"!B 4:B6"),"<=20"))) -- HTH Bob "Joe User" <joeu2004 wrote in message ... 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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multisheet range in SUMPRODUCT?
that requires that Sheet3 exists
This doesn't: =INDEX(FREQUENCY(Sheet1:Sheet4!B4:B6,20),1) -- Biff Microsoft Excel MVP "Joe User" <joeu2004 wrote in message ... I wrote: "Bob Phillips" wrote: =SUMPRODUCT(--(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT("1:4"))&"!B 4: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"}&"!B 4: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")=A 5), 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" <joeu2004 wrote in message ... "Bob Phillips" wrote: =SUMPRODUCT(--(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT("1:4"))&"!B 4: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 ----- "Bob Phillips" wrote in message ... Try =SUMPRODUCT(--(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT("1:4"))&"!B 4:B6"),"<=20"))) -- HTH Bob "Joe User" <joeu2004 wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I delete the same row in a multisheet spreadsheet | Excel Discussion (Misc queries) | |||
Sumproduct with #N/A in range | Excel Worksheet Functions | |||
Select and Insert one sheet from a multisheet excel template | Excel Discussion (Misc queries) | |||
Multisheet formula | Excel Discussion (Misc queries) | |||
sumproduct in a range | Excel Worksheet Functions |