SUMPRODUCT with Today() and text criteria

**Wkruger** · 02-19-2010, 02:33 PM

This is my 1st post and glad to be part of forum

Im attempting to add number of dates within a column (B). I have 2 criteria: Today and person "William". Im using the following and return results is "0"

A: Name
B: Date
C: Type

=SUMPRODUCT(--(Data!$B$2:$B$3100=TODAY()),--(Data!$A$2:$A$3100="William"),--(Data!$B$2:$B$3100))

Once I get this working I would like to adjust for last 30days and look at Type (C) = New

Any suggestions and can send a sample data set. Anyway, any suggestions will be appreciated!

**DonkeyOte** · 02-19-2010, 02:40 PM

Your Date values include Time - you need to compare just the INT portion

=SUMPRODUCT(--(INT(Data!$B$2:$B$3100)=TODAY()),--(Data!$A$2:$A$3100="William"))

(note I also removed the third array - this doesn't really serve any purpose as I see it)

**Wkruger** · 02-19-2010, 05:32 PM

Its worked and THANK YOU!

**Wkruger** · 02-19-2010, 07:19 PM

I have another questions - PLEASE

I need to find the average interval from the Assignment date to Completed date. With conditions of status code: Completed, Service Coodinator of William, Order type of New.

William Completed 17-Feb-10 New 3/1/2010
William Completed 17-Feb-10 New 3/5/2010
William Completed 17-Feb-10 New 4/2/2010
William Completed 16-Feb-10 New 2/19/2010
William Completed 16-Feb-10 New 2/19/2010
Susan Completed 16-Feb-10 New 2/19/2010
William Completed 16-Feb-10 New 2/19/2010
William Completed 16-Feb-10 New 2/19/2010
William Completed 16-Feb-10 New 2/19/2010