I’m trying to do dynamic summing based on two columns using the SumProduct option. In the dashboard design & excel the formulas work but in Preview it just shows #VALUE.
It states that Xcelsius 2008 supports this function so can someone please tell me how to make it work or have another way to get it to work?
Formula Usage:
-Column B on the Data tab has values that need to be match up to the value in AP3 of the current tab (this is updated by Xcelsius based on what the user selected)
-Values in Column G on the Data tab have to match up to the value in AN11 of the current tab (this is updated by Xcelsius based on what the user selected)
-If Column B & Column G match as stated above then it should add the values in Column P
I am also running into the same problem. Can you send me the exact solution you have found. I am using Xcelsius enterprise build 247. Still getting the #value error.
I am using this following formulae and it works great in Excel. However Xcelsius is returning #value error
=SUMPRODUCT(-($K$5:$K$99=S5),-($F$5:$F$99=R5),($L$5:$L$99))
I upgraded to Xcelisus version 5.1.3.0 Build Number 12,1,3,495
After upgrading the SUMPRODUCT does work but the performace goes way down. In the end what I did was have all the data pre-aggregated so when the data comes in I use an Index to pull the rows I want and then a standard SUM for just those rows…
I have the exact same issue. I do have the version mentioned above and it still does not work .
I am trying to get a cell populated with SUMPRODUCT and the cell displays the correct calculated value in Developer mode. The grid and chart displays the correct calculated value in Developer mode. The same formula works well in an Excel also, But when I go to the Preview mode in Xcelcius, I get the #VALUE! Error in the grid and the chart is blank.
Hi Friends,
Thanks for sharing the frustration. I wasted 3-4 days installing and reinstalling the service packs. Still the "Sumproduct "does not work in the preview mode or export. It does look good in the development.
I have version 5.1.3.0 build 12.1.3.495.
Any new ideas.
I wonder why BO could not give us any hint in their bulletins.
You should try to keep complex aggregation logic to a minimum pushing it to the application server if you can but if you must then use sumfi. Sumproduct SUMPRODUCT(array1, array2, array3, ) works, but is not supported the case of SUMPRODUCT((Condition 1) * (Range to Sum)). For example, if you wanted to make a crosstab from tabular data, use =SUMIF(‘Tabular Data’!$A$10:$D$100,$B10&$C$8,‘Tabular Data’!$D$10:$D$100). I have attached an example model. Crosstab Template-sumif2.zip (30.0 KB)