Home |
Search |
Today's Posts |
#1
|
|||
|
|||
SUMPRODUCT across 9 axes
Hi all. I've been developing a sheet that measures numerous aspects of my business and allows any set of parameters to be displayed at a single time. For instance, one could look at sales to New York on a weekly basis in 2004 through a particular channel or customer, then switch these parameters using validated cells to Chicago quarterly 2004 all business. I've created all the sets of vlookups for a SUMPRODUCT to look at, each corresponding to a large data grid that has the appropriate categories (dollars or pounds, weeks, years, customer category, region, etc.). Here is the formula: =SUMPRODUCT(($C22=Data!$M$5:$FN$5)*($O$82=Data!$J$ 7:$J$865)*($A22=Data!$E$7:$E$865)*($B22=Data!$F$7: $F$865)*($E$78:$Q$78=Data!$K$7:$K$865)*($N$116:$Z$ 116=Data!$M$4:$FN$4)*(NOT($D$132:$AD$132=Data!$B$7 :$B$865))*(NOT($F$52:$H$52=Data!$D$7:$D$865))*(NOT ($C$88:$H$88=Data!$C$7:$C$865))*Data!$M$7:$FN$865) where Data!M7:FN865 is the data grid. The NOT functions eliminate certain areas that shouldn't be calculated (i.e. looking at New York you ignore Chicago, LA and Miami, but looking at all business you ignore nothing). This formula returns N/A. If I reduce the ranges to be used or ignored (make $C$88:$H$88 just $C$88) I get a value. How do I overcome this? I have used ranges like this in the past succesfully, but never so many. |
#2
|
|||
|
|||
You don't have to tell excel what not to calculate except that you wouldn't
include it in what you do want to calculate. Also, sumproduct only works on arrays of the same size. E78:Q78 contains many fewer values than K7:K865 does. This seems to be where your NA message is coming from. If you want to do some sumproduct calculating on a single range but "looking for" multiple values, try something like =SUMPRODUCT(--(A1:A10={1,2,3})) which will count cells in A1:A10 that are equal to either 1, 2, or 3. Beyond that, not clear what you're doing. Interesting tidbits about sales to New York and Chicago though.. sounds like business is good. "Cornelius" wrote in message ... Hi all. I've been developing a sheet that measures numerous aspects of my business and allows any set of parameters to be displayed at a single time. For instance, one could look at sales to New York on a weekly basis in 2004 through a particular channel or customer, then switch these parameters using validated cells to Chicago quarterly 2004 all business. I've created all the sets of vlookups for a SUMPRODUCT to look at, each corresponding to a large data grid that has the appropriate categories (dollars or pounds, weeks, years, customer category, region, etc.). Here is the formula: =SUMPRODUCT(($C22=Data!$M$5:$FN$5)*($O$82=Data!$J$ 7:$J$865)*($A22=Data!$E$7: $E$865)*($B22=Data!$F$7:$F$865)*($E$78:$Q$78=Data! $K$7:$K$865)*($N$116:$Z$11 6=Data!$M$4:$FN$4)*(NOT($D$132:$AD$132=Data!$B$7:$ B$865))*(NOT($F$52:$H$52=D ata!$D$7:$D$865))*(NOT($C$88:$H$88=Data!$C$7:$C$86 5))*Data!$M$7:$FN$865) where Data!M7:FN865 is the data grid. The NOT functions eliminate certain areas that shouldn't be calculated (i.e. looking at New York you ignore Chicago, LA and Miami, but looking at all business you ignore nothing). This formula returns N/A. If I reduce the ranges to be used or ignored (make $C$88:$H$88 just $C$88) I get a value. How do I overcome this? I have used ranges like this in the past succesfully, but never so many. |
#3
|
|||
|
|||
Thanks Dave. I read your page on xlDynamic, very nice. Do I understand that if I input the conditional cells in the {x,y,z} format rather than (x:z) this will work? "Dave R." wrote: You don't have to tell excel what not to calculate except that you wouldn't include it in what you do want to calculate. Also, sumproduct only works on arrays of the same size. E78:Q78 contains many fewer values than K7:K865 does. This seems to be where your NA message is coming from. If you want to do some sumproduct calculating on a single range but "looking for" multiple values, try something like =SUMPRODUCT(--(A1:A10={1,2,3})) which will count cells in A1:A10 that are equal to either 1, 2, or 3. Beyond that, not clear what you're doing. Interesting tidbits about sales to New York and Chicago though.. sounds like business is good. "Cornelius" wrote in message ... Hi all. I've been developing a sheet that measures numerous aspects of my business and allows any set of parameters to be displayed at a single time. For instance, one could look at sales to New York on a weekly basis in 2004 through a particular channel or customer, then switch these parameters using validated cells to Chicago quarterly 2004 all business. I've created all the sets of vlookups for a SUMPRODUCT to look at, each corresponding to a large data grid that has the appropriate categories (dollars or pounds, weeks, years, customer category, region, etc.). Here is the formula: =SUMPRODUCT(($C22=Data!$M$5:$FN$5)*($O$82=Data!$J$ 7:$J$865)*($A22=Data!$E$7: $E$865)*($B22=Data!$F$7:$F$865)*($E$78:$Q$78=Data! $K$7:$K$865)*($N$116:$Z$11 6=Data!$M$4:$FN$4)*(NOT($D$132:$AD$132=Data!$B$7:$ B$865))*(NOT($F$52:$H$52=D ata!$D$7:$D$865))*(NOT($C$88:$H$88=Data!$C$7:$C$86 5))*Data!$M$7:$FN$865) where Data!M7:FN865 is the data grid. The NOT functions eliminate certain areas that shouldn't be calculated (i.e. looking at New York you ignore Chicago, LA and Miami, but looking at all business you ignore nothing). This formula returns N/A. If I reduce the ranges to be used or ignored (make $C$88:$H$88 just $C$88) I get a value. How do I overcome this? I have used ranges like this in the past succesfully, but never so many. |
#4
|
|||
|
|||
Hi - I had nothing to do with creating that page. But I agree it is nice.
I just tried, and Excel doesn't seem to accept cell references in place of numbers. Maybe someone can chime in with a way to pull off the (working) equivilant of: =SUMPRODUCT(--(A1:A10={d1,d2,d3})) (which does not work) to count values in A1:A10 that match the content of either D1, D2 or D3. "Cornelius" wrote in message ... Thanks Dave. I read your page on xlDynamic, very nice. Do I understand that if I input the conditional cells in the {x,y,z} format rather than (x:z) this will work? "Dave R." wrote: You don't have to tell excel what not to calculate except that you wouldn't include it in what you do want to calculate. Also, sumproduct only works on arrays of the same size. E78:Q78 contains many fewer values than K7:K865 does. This seems to be where your NA message is coming from. If you want to do some sumproduct calculating on a single range but "looking for" multiple values, try something like =SUMPRODUCT(--(A1:A10={1,2,3})) which will count cells in A1:A10 that are equal to either 1, 2, or 3. Beyond that, not clear what you're doing. Interesting tidbits about sales to New York and Chicago though.. sounds like business is good. "Cornelius" wrote in message ... Hi all. I've been developing a sheet that measures numerous aspects of my business and allows any set of parameters to be displayed at a single time. For instance, one could look at sales to New York on a weekly basis in 2004 through a particular channel or customer, then switch these parameters using validated cells to Chicago quarterly 2004 all business. I've created all the sets of vlookups for a SUMPRODUCT to look at, each corresponding to a large data grid that has the appropriate categories (dollars or pounds, weeks, years, customer category, region, etc.). Here is the formula: =SUMPRODUCT(($C22=Data!$M$5:$FN$5)*($O$82=Data!$J$ 7:$J$865)*($A22=Data!$E$7: $E$865)*($B22=Data!$F$7:$F$865)*($E$78:$Q$78=Data! $K$7:$K$865)*($N$116:$Z$11 6=Data!$M$4:$FN$4)*(NOT($D$132:$AD$132=Data!$B$7:$ B$865))*(NOT($F$52:$H$52=D ata!$D$7:$D$865))*(NOT($C$88:$H$88=Data!$C$7:$C$86 5))*Data!$M$7:$FN$865) where Data!M7:FN865 is the data grid. The NOT functions eliminate certain areas that shouldn't be calculated (i.e. looking at New York you ignore Chicago, LA and Miami, but looking at all business you ignore nothing). This formula returns N/A. If I reduce the ranges to be used or ignored (make $C$88:$H$88 just $C$88) I get a value. How do I overcome this? I have used ranges like this in the past succesfully, but never so many. |
#5
|
|||
|
|||
Dave,
you can use transpose as follows =SUMPRODUCT(--(A1:A10=TRANSPOSE(D1:D3))) since transpose is involved it needs to be array entered and also note that transpose can only take a range D1:D3 as opposed to D1,D2,D3 -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Dave R." wrote in message ... Hi - I had nothing to do with creating that page. But I agree it is nice. I just tried, and Excel doesn't seem to accept cell references in place of numbers. Maybe someone can chime in with a way to pull off the (working) equivilant of: =SUMPRODUCT(--(A1:A10={d1,d2,d3})) (which does not work) to count values in A1:A10 that match the content of either D1, D2 or D3. "Cornelius" wrote in message ... Thanks Dave. I read your page on xlDynamic, very nice. Do I understand that if I input the conditional cells in the {x,y,z} format rather than (x:z) this will work? "Dave R." wrote: You don't have to tell excel what not to calculate except that you wouldn't include it in what you do want to calculate. Also, sumproduct only works on arrays of the same size. E78:Q78 contains many fewer values than K7:K865 does. This seems to be where your NA message is coming from. If you want to do some sumproduct calculating on a single range but "looking for" multiple values, try something like =SUMPRODUCT(--(A1:A10={1,2,3})) which will count cells in A1:A10 that are equal to either 1, 2, or 3. Beyond that, not clear what you're doing. Interesting tidbits about sales to New York and Chicago though.. sounds like business is good. "Cornelius" wrote in message ... Hi all. I've been developing a sheet that measures numerous aspects of my business and allows any set of parameters to be displayed at a single time. For instance, one could look at sales to New York on a weekly basis in 2004 through a particular channel or customer, then switch these parameters using validated cells to Chicago quarterly 2004 all business. I've created all the sets of vlookups for a SUMPRODUCT to look at, each corresponding to a large data grid that has the appropriate categories (dollars or pounds, weeks, years, customer category, region, etc.). Here is the formula: =SUMPRODUCT(($C22=Data!$M$5:$FN$5)*($O$82=Data!$J$ 7:$J$865)*($A22=Data!$E$7: $E$865)*($B22=Data!$F$7:$F$865)*($E$78:$Q$78=Data! $K$7:$K$865)*($N$116:$Z$11 6=Data!$M$4:$FN$4)*(NOT($D$132:$AD$132=Data!$B$7:$ B$865))*(NOT($F$52:$H$52=D ata!$D$7:$D$865))*(NOT($C$88:$H$88=Data!$C$7:$C$86 5))*Data!$M$7:$FN$865) where Data!M7:FN865 is the data grid. The NOT functions eliminate certain areas that shouldn't be calculated (i.e. looking at New York you ignore Chicago, LA and Miami, but looking at all business you ignore nothing). This formula returns N/A. If I reduce the ranges to be used or ignored (make $C$88:$H$88 just $C$88) I get a value. How do I overcome this? I have used ranges like this in the past succesfully, but never so many. |
#6
|
|||
|
|||
Unfortunately this does not seem to work. I have succesfully used this technique a number of times with different sized arrays (but smaller formulas) so long as they refer to different worksheets. However, I have not been able to predict if they will work or not; sometimes I merely have to adjust the size of the array, sometimes I have to change the orientation of the array from vertical to horizontal. By fiddling with the parameters I can produce results, but not by any logical path that I can see. Is there a rule within SUMPRODUCT that seeks certain array sizes or shapes? If so, is it affecting the aforementioned formula? Thanks. "Peo Sjoblom" wrote: Dave, you can use transpose as follows =SUMPRODUCT(--(A1:A10=TRANSPOSE(D1:D3))) since transpose is involved it needs to be array entered and also note that transpose can only take a range D1:D3 as opposed to D1,D2,D3 -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Dave R." wrote in message ... Hi - I had nothing to do with creating that page. But I agree it is nice. I just tried, and Excel doesn't seem to accept cell references in place of numbers. Maybe someone can chime in with a way to pull off the (working) equivilant of: =SUMPRODUCT(--(A1:A10={d1,d2,d3})) (which does not work) to count values in A1:A10 that match the content of either D1, D2 or D3. "Cornelius" wrote in message ... Thanks Dave. I read your page on xlDynamic, very nice. Do I understand that if I input the conditional cells in the {x,y,z} format rather than (x:z) this will work? "Dave R." wrote: You don't have to tell excel what not to calculate except that you wouldn't include it in what you do want to calculate. Also, sumproduct only works on arrays of the same size. E78:Q78 contains many fewer values than K7:K865 does. This seems to be where your NA message is coming from. If you want to do some sumproduct calculating on a single range but "looking for" multiple values, try something like =SUMPRODUCT(--(A1:A10={1,2,3})) which will count cells in A1:A10 that are equal to either 1, 2, or 3. Beyond that, not clear what you're doing. Interesting tidbits about sales to New York and Chicago though.. sounds like business is good. "Cornelius" wrote in message ... Hi all. I've been developing a sheet that measures numerous aspects of my business and allows any set of parameters to be displayed at a single time. For instance, one could look at sales to New York on a weekly basis in 2004 through a particular channel or customer, then switch these parameters using validated cells to Chicago quarterly 2004 all business. I've created all the sets of vlookups for a SUMPRODUCT to look at, each corresponding to a large data grid that has the appropriate categories (dollars or pounds, weeks, years, customer category, region, etc.). Here is the formula: =SUMPRODUCT(($C22=Data!$M$5:$FN$5)*($O$82=Data!$J$ 7:$J$865)*($A22=Data!$E$7: $E$865)*($B22=Data!$F$7:$F$865)*($E$78:$Q$78=Data! $K$7:$K$865)*($N$116:$Z$11 6=Data!$M$4:$FN$4)*(NOT($D$132:$AD$132=Data!$B$7:$ B$865))*(NOT($F$52:$H$52=D ata!$D$7:$D$865))*(NOT($C$88:$H$88=Data!$C$7:$C$86 5))*Data!$M$7:$FN$865) where Data!M7:FN865 is the data grid. The NOT functions eliminate certain areas that shouldn't be calculated (i.e. looking at New York you ignore Chicago, LA and Miami, but looking at all business you ignore nothing). This formula returns N/A. If I reduce the ranges to be used or ignored (make $C$88:$H$88 just $C$88) I get a value. How do I overcome this? I have used ranges like this in the past succesfully, but never so many. |
#7
|
|||
|
|||
Cornelius, from the sound of it you appear to be doing the kind of analysis
that pivot tables were designed for. Have you looked at using those for your data at all? http://peltiertech.com/Excel/Pivots/pivotstart.htm -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Cornelius" wrote in message ... Hi all. I've been developing a sheet that measures numerous aspects of my business and allows any set of parameters to be displayed at a single time. For instance, one could look at sales to New York on a weekly basis in 2004 through a particular channel or customer, then switch these parameters using validated cells to Chicago quarterly 2004 all business. I've created all the sets of vlookups for a SUMPRODUCT to look at, each corresponding to a large data grid that has the appropriate categories (dollars or pounds, weeks, years, customer category, region, etc.). Here is the formula: =SUMPRODUCT(($C22=Data!$M$5:$FN$5)*($O$82=Data!$J$ 7:$J$865)*($A22=Data!$E$7: $E$865)*($B22=Data!$F$7:$F$865)*($E$78:$Q$78=Data! $K$7:$K$865)*($N$116:$Z$11 6=Data!$M$4:$FN$4)*(NOT($D$132:$AD$132=Data!$B$7:$ B$865))*(NOT($F$52:$H$52=D ata!$D$7:$D$865))*(NOT($C$88:$H$88=Data!$C$7:$C$86 5))*Data!$M$7:$FN$865) where Data!M7:FN865 is the data grid. The NOT functions eliminate certain areas that shouldn't be calculated (i.e. looking at New York you ignore Chicago, LA and Miami, but looking at all business you ignore nothing). This formula returns N/A. If I reduce the ranges to be used or ignored (make $C$88:$H$88 just $C$88) I get a value. How do I overcome this? I have used ranges like this in the past succesfully, but never so many. |
#8
|
|||
|
|||
Main thing to ensure is that all ranges are the same size, albeit there is
an exception to that when using TRANSPOSE to flip a range when testing against each of the elements. You also cannot use whole column references in your formulas. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- <snip |
#9
|
|||
|
|||
Dave R. wrote:
Hi - I had nothing to do with creating that page. But I agree it is nice. I just tried, and Excel doesn't seem to accept cell references in place of numbers. Maybe someone can chime in with a way to pull off the (working) equivilant of: =SUMPRODUCT(--(A1:A10={d1,d2,d3})) (which does not work) to count values in A1:A10 that match the content of either D1, D2 or D3. [..] =SUMPRODUCT(--ISNUMBER(MATCH(A1:A10,D1:D3,0))) |
#10
|
|||
|
|||
Looks interesting. I'm not sure it will give me the detail I'm looking for, but it is definitely worth trying, thanks Ken. On another note, I suspect I can achieve some of the results I was looking for by eliminating some of the axes (I'll just calculate the possible choices on the data field first), and using OFFSET to redfine the ranges for time period (i.e. IF("Quarterly",OFFSET(A1:Z1,x),IF("Monthly",OFFSET (A1:Z1,y)... Thanks for all the help. If someone has an epiphany, I'd love to know the solution. ccc "Ken Wright" wrote: Cornelius, from the sound of it you appear to be doing the kind of analysis that pivot tables were designed for. Have you looked at using those for your data at all? http://peltiertech.com/Excel/Pivots/pivotstart.htm -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Cornelius" wrote in message ... Hi all. I've been developing a sheet that measures numerous aspects of my business and allows any set of parameters to be displayed at a single time. For instance, one could look at sales to New York on a weekly basis in 2004 through a particular channel or customer, then switch these parameters using validated cells to Chicago quarterly 2004 all business. I've created all the sets of vlookups for a SUMPRODUCT to look at, each corresponding to a large data grid that has the appropriate categories (dollars or pounds, weeks, years, customer category, region, etc.). Here is the formula: =SUMPRODUCT(($C22=Data!$M$5:$FN$5)*($O$82=Data!$J$ 7:$J$865)*($A22=Data!$E$7: $E$865)*($B22=Data!$F$7:$F$865)*($E$78:$Q$78=Data! $K$7:$K$865)*($N$116:$Z$11 6=Data!$M$4:$FN$4)*(NOT($D$132:$AD$132=Data!$B$7:$ B$865))*(NOT($F$52:$H$52=D ata!$D$7:$D$865))*(NOT($C$88:$H$88=Data!$C$7:$C$86 5))*Data!$M$7:$FN$865) where Data!M7:FN865 is the data grid. The NOT functions eliminate certain areas that shouldn't be calculated (i.e. looking at New York you ignore Chicago, LA and Miami, but looking at all business you ignore nothing). This formula returns N/A. If I reduce the ranges to be used or ignored (make $C$88:$H$88 just $C$88) I get a value. How do I overcome this? I have used ranges like this in the past succesfully, but never so many. |
#11
|
|||
|
|||
"Ken Wright" wrote...
Main thing to ensure is that all ranges are the same size, albeit there is an exception to that when using TRANSPOSE to flip a range when testing against each of the elements. You also cannot use whole column references in your formulas. .... It's not an exception. All Ranges must be the same size, but =SUMPRODUCT(--(A1:A100=TRANSPOSE(B1:B5))) has *ONE* argument, so it's necessarily the same size as itself. As for the A1:A100=TRANSPOSE(B1:B5) term, it's a derived array reflecting well-defined semantics. To wit, the arrays on either side of the = are confomant, i.e., either MMULT(LHS,RHS) or MMULT(RHS,LHS) evaluates numerically. |
#12
|
|||
|
|||
"Cornelius" wrote...
Hi all. I've been developing a sheet that measures numerous aspects of my business and allows any set of parameters to be displayed at a single time. For instance, one could look at sales to New York on a weekly basis in 2004 through a particular channel or customer, then switch these parameters using validated cells to Chicago quarterly 2004 all business. I've created all the sets of vlookups for a SUMPRODUCT to look at, each corresponding to a large data grid that has the appropriate categories (dollars or pounds, weeks, years, customer category, region, etc.). Here is the formula: [reformatted w/inline comments] =SUMPRODUCT(($C22=Data!$M$5:$FN$5) OK, so this deals selecting columns in M:FN. This gives a 1 row by 158 column array. *($O$82=Data!$J$7:$J$865) *($A22=Data!$E$7:$E$865) *($B22=Data!$F$7:$F$865) And these deal with selecting rows in 7:865. Together, these give an 859 row by 1 column range. With the first term, this gives an 859 row by 158 column array. So far, so good. *($E$78:$Q$78=Data!$K$7:$K$865) This is trouble. This gives an 859 by 13 array. This doesn't match up with the preceding cumulative 859 by 158 array. You can't do that. If you mean by this term selecting rows that satisfy any of the criteria in E78:Q78 (OR criteria), then you need to make this *(COUNTIF($E$78:$Q$78,Data!$K$7:$K$865)0) which gives an 859 by 1 array (of 1s and 0s), which *IS* conformant with the previous cumulative array. *($N$116:$Z$116=Data!$M$4:$FN$4) This is purely nonconformant. It appears to be a set of OR criteria to select columns, so should be replaced by *(COUNTIF($N$116:$Z$116,Data!$M$4:$FN$4)0) which gives a 1 by 158 array (of 1s and 0s), which is conformant with the previous cumulative array. *(NOT($D$132:$AD$132=Data!$B$7:$B$865)) *(NOT($F$52:$H$52=Data!$D$7:$D$865)) *(NOT($C$88:$H$88=Data!$C$7:$C$865)) Are these 'NOR' criteria, meaning are these rows selected only if they match none of the values in D132:AD132, F52:H52, C88:H88, respectively? If so, *(COUNTIF($D$132:$AD$132,Data!$B$7:$B$865)=0) *(COUNTIF($F$52:$H$52,Data!$D$7:$D$865)=0) *(COUNTIF($C$88:$H$88,Data!$C$7:$C$865)=0) *Data!$M$7:$FN$865) More robust and efficient to make this ,Data!$M$7:$FN$865) that is, a separate argument. So your final formula would become the monstrous =SUMPRODUCT(($C22=Data!$M$5:$FN$5) *($O$82=Data!$J$7:$J$865) *($A22=Data!$E$7:$E$865) *($B22=Data!$F$7:$F$865) *(COUNTIF($E$78:$Q$78,Data!$K$7:$K$865)0) *(COUNTIF($N$116:$Z$116,Data!$M$4:$FN$4)0) *(COUNTIF($D$132:$AD$132,Data!$B$7:$B$865)=0) *(COUNTIF($F$52:$H$52,Data!$D$7:$D$865)=0) *(COUNTIF($C$88:$H$88,Data!$C$7:$C$865)=0), Data!$M$7:$FN$865) |
#13
|
|||
|
|||
Not sure how sensitive your data is, but I'll happily take a stab at
matching the output from any of your reports with a Pivot Table report if you want. You would need to take the NOSPAM bit out of my email addy, and give me an idea of an existing report format. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Cornelius" wrote in message ... Looks interesting. I'm not sure it will give me the detail I'm looking for, but it is definitely worth trying, thanks Ken. On another note, I suspect I can achieve some of the results I was looking for by eliminating some of the axes (I'll just calculate the possible choices on the data field first), and using OFFSET to redfine the ranges for time period (i.e. IF("Quarterly",OFFSET(A1:Z1,x),IF("Monthly",OFFSET (A1:Z1,y)... Thanks for all the help. If someone has an epiphany, I'd love to know the solution. ccc "Ken Wright" wrote: Cornelius, from the sound of it you appear to be doing the kind of analysis that pivot tables were designed for. Have you looked at using those for your data at all? http://peltiertech.com/Excel/Pivots/pivotstart.htm -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 -------------------------------------------------------------------------- -- It's easier to beg forgiveness than ask permission :-) -------------------------------------------------------------------------- -- "Cornelius" wrote in message ... Hi all. I've been developing a sheet that measures numerous aspects of my business and allows any set of parameters to be displayed at a single time. For instance, one could look at sales to New York on a weekly basis in 2004 through a particular channel or customer, then switch these parameters using validated cells to Chicago quarterly 2004 all business. I've created all the sets of vlookups for a SUMPRODUCT to look at, each corresponding to a large data grid that has the appropriate categories (dollars or pounds, weeks, years, customer category, region, etc.). Here is the formula: =SUMPRODUCT(($C22=Data!$M$5:$FN$5)*($O$82=Data!$J$ 7:$J$865)*($A22=Data!$E$7: $E$865)*($B22=Data!$F$7:$F$865)*($E$78:$Q$78=Data! $K$7:$K$865)*($N$116:$Z$11 6=Data!$M$4:$FN$4)*(NOT($D$132:$AD$132=Data!$B$7:$ B$865))*(NOT($F$52:$H$52=D ata!$D$7:$D$865))*(NOT($C$88:$H$88=Data!$C$7:$C$86 5))*Data!$M$7:$FN$865) where Data!M7:FN865 is the data grid. The NOT functions eliminate certain areas that shouldn't be calculated (i.e. looking at New York you ignore Chicago, LA and Miami, but looking at all business you ignore nothing). This formula returns N/A. If I reduce the ranges to be used or ignored (make $C$88:$H$88 just $C$88) I get a value. How do I overcome this? I have used ranges like this in the past succesfully, but never so many. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT help | Excel Worksheet Functions | |||
Another Sumproduct & #N/A problem | Excel Worksheet Functions | |||
Sumproduct ... Empty Cells vs Spaces? | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |