array formula problem

[Richard Yates]

Excel array formulas drive me crazy. I think part of my brain formed early
under the influence of Fortran (yes I know that dates me).

I have two columns of data named 'Date_In' and 'Date_Out. I want to count
all of those rows in which 'Date_In' is before a certain date ('Then') and
'Date_Out' as after another date ('Now'). Why doesn't this work?

{=Sum(if(and(Date_In<Then,Date_Out>Now),1,0))}

Richard Yates

 

[Dave Peterson]

What happens when you tried it?

Do Date_In and Date_out refer to whole columns (array formulas don't like whole
columns).

Are now and then named values? I'd stay away from use now as a name--it looks
too close to the built in =Now() function.

=sumproduct(--(a1:a10<date(2005,9,1)),--(b1:b10>date(2005,9,30)))
or
=sumproduct(--(a1:a10<date(2005,9,1)),--(b1:b10>today()))

maybe another way to accomplish the same thing.

=sumproduct() likes to work with numbers. -- changes trues and falses to
+1/0's.

 

[Richard Yates]

Thanks, Dave. sumproduct worked like a charm (after I used a couple of other
magic spells).

(array formulas don't like whole columns).<

This helped, too. And along the way I discovered that array formulas also do
not like AND(array1,array2). In fact it just does not work at all.

Richard