Using SUMPRODUCT...but prolly not correctly

[Guest]

I posted this a few hours ago. I know it's annoying to repost but and I
apologize but I read my own post and confused myself. So I wanted to make my
post clearer so I could I understand it at least. This one is the last hurdle
on a file I have due tommorrow so any help is profoundly appreciated. I would
like to show If an employee has had points accumulated during the last month
from today, the previous month from today and the 2 months previous to today.
dec25 2004: jan24 2005 if there is one absence than 1
nov25 2004: dec24 2004 if there are 2 absences than 2
oct25 2004: nov24 2004 if there is one absence than 1

the dates are in D150269
the corresponding points N150:N269
3 cells to return data are m10 m11 and m12
for the last month (m11) I'm using this

=SUMPRODUCT(($D$150:$D$269>=DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())))*(TODAY()>=$D$150:$D$269)*$N$150:$N$269)

in m12 for the previous month I'm using the following but receiving a #value
error


=SUMPRODUCT(($D$150:$D$269>=DATE(YEAR(TODAY()),MONTH(TODAY())-2,DAY(TODAY())))*(DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())))>=$D$150:$D$269)*$N$150:$N$269

Please help and I appreciate any effort. Thanks!

 

[Biff]

Hi!

In your first formula:

=SUMPRODUCT(($D$150:$D$269>=DATE(YEAR(TODAY()),MONTH(TODAY
())-1,DAY(TODAY())))*(TODAY()>=$D$150:$D$269)
*$N$150:$N$269)

you don't need:

*(TODAY()>=$D$150:$D$269)

Your second formula is missing a ) at the very end and has
one too many ) here at the end:

*(DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())))

Biff