Array fomrula not working...

[Guest]

Hello.

I have two columns of data - Column B is dates formatted as date (i.e.
3/10/2007), and column C is times formatted as time (i.e. 6:00 AM). Problem
I'm having is that when I run an array to search for values that meet my
criteria in both columns, I get 0, where I know that there are matching
values. I use the following array formula:
{=SUM((B$1:B$100="3/10/2007")*(C$1:C$100="6:00 AM"))}.

There are 7 entires that meet this particular criteria, however, the formula
gives me 0. ANy assitance with tis is appreciated.

Note that when I run seperate COUNTIF statements in each column, I get the
desired result, but only for the values in one column - I did this as a test
to ascertain if I was using the proper syntax.

Thank you.

NWO

 

[Bernie Deitrick]

NWO,

=SUM((B$1:B$100=DATEVALUE("3/10/2007"))*((C$1:C$100)=TIMEVALUE("6:00 AM")))

HTH,
Bernie
MS Excel MVP

 

[T. Valko]

Try this (normally entered, not an array):

=SUMPRODUCT(--(B1:B10=DATE(2007,3,10)),--(C1:C10=TIME(6,0,0)))

Your array formula will work like this:

=SUM((B$1:B$10=--"3/10/2007")*(C$1:C$10=--"6:00 AM"))

Biff

 

[Guest]

Thank you. Works for AM only, though. PM times appear to be ignored. Help!!!

NWO

 

[Harlan Grove]

Your array formula will work like this:

=SUM((B$1:B$10=--"3/10/2007")*(C$1:C$10=--"6:00 AM"))

....

And if the col B values are integers,

=COUNT(1/(B$1:B$10+C$1:C$10=--"3/10/2007 6:00 AM"))

 

[Guest]

Col B are date formatted, col C are time formatted. Problem is formula won;t
work with PM values (i.e. 6:00 PM). Help!!!

Thanx.

NWO