Maybe this is old... sorry if it is. I use another way to accomplish this. I learned this function here, in the mailing list. I'm just applying it differently.
Imagine you have a calc document with dates on column b, status on column c and student name on column d. First of all... you must agree with me if I say that multiplying something like this: 1*1*1*1*1*1 equals always to 1. Right?. But if at least one of those "ones" is a zero, the result it's always zero. Right? With that idea in mind, this is the formula I use: SUM.PRODUCT Imagine you defined some named cell ranges, so this is simple: =SUM.PRODUCT((DateRange>=BeginningDate)*(DateRange<=EndingDate)*(StatusRange=AStatus)) If you don't like the named cell ranges this is what it's like without them: =SUM.PRODUCT(($B$4:$B$243>=$C$247)*($B$4:$B$243<=$D$247)*($C$4:$C$243=E248)) Note that I'm not using semicolons to separate each part of the formula. This is because this way you can export the document to Excel without problems. Here I attach a calc document with an example Hope it's clear and helpful. Ely 2008/10/25 Brian Barker <[EMAIL PROTECTED]> > At 20:51 25/10/2008 +0000, Girard Aquino wrote: > >> I totally had problems with syntax and after reviewing wild characters, I >> got it to work with this: >> =COUNTIF($Scratch.A2:A123;"\"). >> > > Sorry, but - as I said before - you have not begun to explain what you are > doing here. You appear to be counting cells that contain just a backslash, > but you have resisted explaining why. That's perfectly OK, of course, but > it makes it impossible to answer your questions! In my original example, > you would be using something like COUNTIF(A1:A99;"22008attended") or perhaps > COUNTIF(A1:A99;G10), where G10 contained the relevant string. There were no > backslashes there. > > But I had to change 1 to 2 for Feb, 3 for March, and so on... >> > > I always thought February *was* the second month! Is it the first month in > your calendar? If not, why are you having to change anything? > > is there a simpler or other automated way of doing the formula you could >> think of? (just for learning's sake) thank you. >> > > Perhaps something closer to what I suggested. ;^) > > At 23:45 25/10/2008 +0000, Girard Aquino wrote: > >> I've noticed that enabling regular expressions in formulas has to be >> ticked every time you run OOo. when you close it, the option is unticked. is >> this really the case for that option or a bug in OOo 3.0 on XP? >> > > Neither. I've just checked this on my installation (3.0 on Windows XP) and > that option definitely sticks. It seems to be saved in documents and > perhaps also in the default template, so you should not see the behaviour > your describe. > > at first i thought there was something wrong with the formula since I got >> zeros on the cells when I opened the file again. one would think that >> ticking this option once should do the trick and not have to do it every >> time. >> > > It does for me. I cannot guess what you are doing that is different. > > > Brian Barker > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > >
DateRangeAttendedCalculation.ods
Description: application/vnd.oasis.opendocument.spreadsheet
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
