SUMPRODUCT + DATEVALUE

[Guest]

Hi everyone -- first off, the answers I get are SO INCREDIBLY helpful. THANK
YOU!

Okay -- here's what I'm trying to do. I have a spreadsheet that looks like:

A1 (campaign start date): 3/15/07
A2 (campaign end date): 5/4/07

Row 6 -- 1/1/07 1/2/07 1/3/07 1/4/07 ... 12/31/07
Row 7 -- 1 5 2 4 7

I would like to add row 7 with the campaign date range. Here is the formula
I'm trying, but it is not working:

=SUMPRODUCT(--(AND(DATEVALUE(A6:Z6)>=A1,DATEVALUE(A6:Z6)<=A2)),--(A6:Z6<>""),A7:Z7)

Any thoughts out there? This isn't working -- I get #VALUE!.

Thanks much.

Ellen

 

[Dave Peterson]

Are they really dates--or just text masquerading as dates?

How about the values in a7:z7? Are they numbers or text?

Just changing the format isn't enough.

Can you pick out one of the columns you _know_ should increment that sum, and
format those cells as General and then retype the values--both the date and the
value--what happens to the formula.

 

[Guest]

Try:

=SUMPRODUCT((A6:Z6>=A1)*(A6:Z6<=A2)*(A7:Z7))

All dates assumed to be DATE format cells.

 

[Guest]

YOU ROCK!!! Thank you. I had checked all the cells originally to make sure
that numbers were formatted as numbers and dates as dates. However, A1 and A2
(start and end dates) were links to something else and not acting as dates.
Once I changed this, my formula worked.

THANK YOU SO MUCH.

Ellen

 

[Dave Peterson]

Glad you fixed the problem.

YOU ROCK!!! Thank you. I had checked all the cells originally to make sure
that numbers were formatted as numbers and dates as dates. However, A1 and A2
(start and end dates) were links to something else and not acting as dates.
Once I changed this, my formula worked.

THANK YOU SO MUCH.

Ellen