# SumProduct Function

M

#### mp80237

I am using Excel 2007. I have it looking at dates and a couple requirments
in the formula. N\$1 is my date of 01/03/2008. The change details tab lists
all of the records. When I use this formula for a monthly report (using date
above January, 2008), it pulls the data correctly. But I need it for daily
activity. Below is my formula.

=SUMPRODUCT(--(Change_Details!\$A\$2:\$A\$1499-DAY(Change_Details!\$A\$2:\$A\$1499)+1=DATE(YEAR(N\$1),MONTH(N\$1),1)),--(ISNUMBER(SEARCH(\$L\$3,Change_Details!\$Q\$2:\$Q\$1499)+(SEARCH(\$L\$1,Change_Details!\$B\$2:\$B\$1499)))))

For example it came back as 3 records for the month of January. When I put
01/03/2008 into cell N\$1 and change my formula (see below), it doesnâ€™t work.
Below is the changed formula.

=SUMPRODUCT(--(Change_Details!\$A\$2:\$A\$1499-DAY(Change_Details!\$A\$2:\$A\$1499)+1=DATE(YEAR(N\$1),MONTH(N\$1),DAY(N\$1))),--(ISNUMBER(SEARCH(\$L\$3,Change_Details!\$Q\$2:\$Q\$1499)+(SEARCH(\$L\$1,Change_Details!\$B\$2:\$B\$1499)))))

Am I getting something wrong? I comes back as 0 records

B

#### bpeltzer

The first part of your function is forcing the comparison date on the
Change_Details sheet to be the first of the month:
Change_Details!\$A\$2:\$A\$1499-DAY(Change_Details!\$A\$2:\$A\$1499)+1
If you want to compare actual dates the formula gets a lot simpler, as you
don't have to adjust the Change_Detail date. You also don't need to break N1
into components (year, month, day) just to put them back together
=SUMPRODUCT(--(Change_Details!\$A\$2:\$A\$1499=\$N\$1),--(ISNUMBER(SEARCH(\$L\$3,Change_Details!\$Q\$2:\$Q\$1499)+(SEARCH(\$L\$1,Change_Details!\$B\$2:\$B\$1499)))))
(I'm assuming that N1 and the dates in Change_Details!A:A are JUST dates,
with no time specified. If that's not true, you'll need to calculate away
the time components)

T

#### T. Valko

If N1 = 1/3/2008 .....

The only difference between the 2 formulas is that the first one is "looking
for" 1/1/2008 and the second one is "looking for" 1/3/2008.

T