"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)
|