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. |
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 |