View Single Post
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
sweens319 sweens319 is offline
external usenet poster
 
Posts: 18
Default Compare/Find Exact Date match with varying time

Could the answer be a combination of both of these attempts (David &
Teethless)?

I don't think putting quotes around IDX is correct, because IDX is a named
array. But, then again, I could be wrong, because without the quotes I get
#N/A for the result, and with the quotes I get $0 (14 is the correct answer).

Thank you for your continued help.

"Teethless mama" wrote:

Put quotes around the "IDX"

=SUMPRODUCT(--(CSBD!K2:K1000="IDX"),--(INT(CSBD!$H2:H1000)=--"9/2/2007"))


"sweens319" wrote:

Getting:

#N/A

using that one.

"Teethless mama" wrote:

Try this:

=SUMPRODUCT(--(CSBD!K2:K1000=IDX),--(INT(CSBD!$H2:H1000)=--"9/2/2007"))


"sweens319" wrote:

I have a column that is formatted with a date and timestamp
10/4/2007 14:21

I want to find all the files that were completed on a specific date at
varying times.
I searched the help and found a little on criteria using wildcards, but I
can't seem to figure out the correct way to write my formula.

I tried the following and keep getting a result of 0 (manual calculation
proves the correct answer is 0):

$H2:H1000="9/2/2007*" - 0
$H2:H1000="9/2/2007"* - error
$H2:H1000=9/2/2007* - error

Perhaps my whole formula is incorrect somehow. I've got multiple criteria,
one being matching a separate columns value to an array (thank you to the
answer-people on my previous posts for making this possible for me).

=SUMPRODUCT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/2/2007*"))