2011/6/2 Michael Adams <[email protected]>: > On Friday 03 June 2011 00:56, Johnny Rosenberg wrote: >> 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… >> > > Go on Johnny, it's not a tutorial without an example. Else it's just a trumpet > blow. > > -- > Michael
Well, I can give an example similar to the one that I solved with SUMPRODUCT(). Let's say that every time we bought something we enter all the details about it in a spreadsheet. The fields could be: Date Name of the thing we bought (like Milk, Toaster or whatever) Some kind of amount, like weight, volume or something What we paid Some kind of category of the product, like food, clothes and office More things, could be almost anything… On another spreadsheet we want to keep track of how much money we spend on food the last month, so I set up a table with 28-31 rows depending on which month we are studying. If it is 2011-06-03 today, the table would start at 2011-05-04 and end at 2011-06-03. To calculate how much money we spent on food we now need two conditions to be fulfilled for every row: Date and Category. Let's say that we use the A column for date and the sheet were we have our data is called ”Data”, then B8 could look something like this: =SUMPRODUCT(Data.A2:A100000=A8;Data.D2:D100000="Food";Data.F2:F100000) In this made-up example (which I did not test, so I hope there are no typos in it…), the Data sheet has date in column A, Categories in column D and Cost in column F. So what it does, is simply to add every thing in column F (well, at least row 2 until row 100000) for all the rows where column Data.A match A8 (which is a date) and where column Data.D match the word ”Food”. One thing I miss though, is something that I could do in Excel. In Excel ”A:A” means ”the whole A column”. I haven't found an easy way to do that in LibreOffice (or OpenOffice.org). Kind regards Johnny Rosenberg ジョニー・ローゼンバーグ -- ----------------------------------------------------------------- To unsubscribe send email to [email protected] For additional commands send email to [email protected] with Subject: help
