Formula help for counting,with a column of dates and a column of n

Got a column of names and a corresponding column of dates. Am trying to
figure out how to write a formula that will a) select a pre-specified name
from the first column and count the number of times it occurs BASESD ON b)
the dates in the second column if they fall within a specified range.

If the date in the date column is within (the last week), then count the
name in the corresponding name column if it's (name goes here).

Both lists have data added to them daily and I don't want to get in and have
to move ranges or anything else manually every day.

How do I do that?

Assuming that data (and future data to be added) are in range A2:B1000.
Assuming requested name is in K1.

=SUMPRODUCT(--($A$2:$A$1000=K1)*--($B$2:$B$1000>=TODAY()-6))

or, for any range of dates, earlier in L1 and later in M1,

=SUMPRODUCT(--($A$2:$A$1000=K1)*--($B$2:$B$1000>=L1))*--($B$2:$B$1000<=M1))

HTH
Kostis Vezerides

This formula finally worked. Thank you! When I attempt to change the range
from $E$2:$E$105 to anything else (specifically the 105 to any other number)
I get an #N/A message in the formula cell. Why? How can I increase the field
range to 1000??


"vezerid" wrote:

> Assuming that data (and future data to be added) are in range A2:B1000.
> Assuming requested name is in K1.
>
> =SUMPRODUCT(--($A$2:$A$1000=K1)*--($B$2:$B$1000>=TODAY()-6))
>
> or, for any range of dates, earlier in L1 and later in M1,
>
> =SUMPRODUCT(--($A$2:$A$1000=K1)*--($B$2:$B$1000>=L1))*--($B$2:$B$1000<=M1))
>
> HTH
> Kostis Vezerides
>
>