Medellin, June 2 2011 When I use the SUMPRODUCT function, usually I do involving specific range names and relational operators (>,<,<>,>=,> =, =), , as shown in this example: SUMPRODUCT((Local_de=A2)*(date_Event=B2)*(Concept_de="Applied")) ,which could be read as "get the sum of all values that have: local name equal to cell A2, and date equal to cell B2 and concept equal Applied (here the symbol product *, applies to a construction and, intercepting sets: local_de , date_event and concept_de).
I also use the enormous power of that function, with multiple interceptions and respective arithmetic operators, creating events of formulation real complex. This example shows is the ability: =SUMPRODUCT((Fecha_de_evento>=Fecha.INI)*(Iva_de=0,1)*(Fecha_de_evento<=Fecha.FIN)*(Local_de=Local_re)*(Concepto_de="venta de contado")*(Valor))+SUMPRODUCT((Fecha_de_evento>=Fecha.INI)*(Iva_de=0,1)*(Fecha_de_evento<=Fecha.FIN)*(Local_de=Local_re)*(Concepto_de="Venta a Crédito")*(Valor)). This approach also has a huge advantage, because its execution time is significantly effective when compared to the classical functions. In fact in MS Office 2007/2010 have included a couple of new features and COUNTIFSET and SUNIFSET, that do not exist in OOo / Libo any version and I've replaced without any problem with our SUMPRODUCT function with options and methods shown above. Regards, Luis E. Vásquez R. OpenOffice.org Volunteer & Support Este mensaje se ha enviado desde Medellín, Colombia *10 Años usando exitosamente OpenOffice.org libre, seguro y abierto * <http://facebook.com/> Facebook <http://facebook.com/> 2011/6/2 Todd Wilson <[email protected]> > I want to second Johnny's praise of SUMPRODUCT. It is very often > exactly what I need as well. Let me give an example. I'm a teacher, > and I have student grades in a spreadsheet, with students as rows, > assignments as columns, and grades in the intersecting cells. Above the > student grades, I have two special rows: points possible and weight for > each assignment. So it looks something like > > ... 8 9 10 ... 20 ... > A > B As1 As2 As3 Ask (assignments, tests, etc.) > C Weights w1 w2 w3 ... wk > D Pts possible n1 n2 n3 ... nk > E Student 1 s11 s12 s13 ... s1k > F Student 2 s21 s22 s23 ... s2k > .. > > where weights are between 0 and 1 and total to 1, and each sij is > between 0 and nj. Now I can compute Student 1's total as a number > between 0 and 1 using > > =SUMPRODUCT(E8:E20; 1/$D8:$D20; $C8:$C20) > > and copy this formula down the column for all students. I can even copy > the formula up to row C, where I should get 1.0000 as the answer, to > verify that the weights add up correctly. > > Sorry if this is a bit off-topic, but I agree with Johnny that this is a > feature of the spreadsheet worth trumpeting. > > Todd Wilson > > > Johnny Rosenberg wrote, on 06/02/2011 05:56 AM: > > It's unbelievable. Since 1999 I have used > > Excel/OpenOffice.org/LibreOffice quite a lot, almost daily. I consider > > some of my spreadsheets somewhat advanced with macros and long > > formulas doing some fancy stuff… > > > > Today I accidently ran into the SUMPRODUCT() function and found that > > it's exactly what I really needed for all those years! How could I > > manage without it? Until today I could, but now I reached the limits > > of the combination My brain + No SUMPRODUCT() function… > > So I started to search the Internet for how to solve a problem that I > > had, which I couldn't solve with SUMIF or other functions and I found > > some SUMIF examples. The last example of one web page was however a > > SUMPRODUCT() example which solved my problem very easily. I could even > > remove three entire columns in my spreadsheet, every one of which > > included some complex formulas… > > > > I guess the lack of knowledge of the SUMPRODUCT() function forced me > > to learn quite a few other functions, workarounds and tricks… > > > > Maybe this would rather be sent to the Discuss list, but I thought > > that this could be a hint for other people who didn't know about the > > SUMPRODUCT() function for some strange reason… > > > > > > Kind regards > > > > Johnny Rosenberg > > ジョニー・ローゼンバーグ > > > -- > ----------------------------------------------------------------- > To unsubscribe send email to [email protected] > For additional commands send email to [email protected] > with Subject: help > > -- ----------------------------------------------------------------- To unsubscribe send email to [email protected] For additional commands send email to [email protected] with Subject: help
