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

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.

Ellen G wrote:

We're getting closer. My formula now looks like this:

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

I'm no longer getting an error, but I'm not getting a sum of row 7. I'm
ending up with 0. I have double checked to make sure I have values in row 7
within the campaign range -- I do indeed. So any thoughts?

Thanks so much.

Ellen

"Toppers" wrote:

Try:

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

All dates assumed to be DATE format cells.

"Ellen G" wrote:

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

Try:

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

All dates assumed to be DATE format cells.

"Ellen G" wrote:

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

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" wrote:

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.

Ellen G wrote:

We're getting closer. My formula now looks like this:

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

I'm no longer getting an error, but I'm not getting a sum of row 7. I'm
ending up with 0. I have double checked to make sure I have values in row 7
within the campaign range -- I do indeed. So any thoughts?

Thanks so much.

Ellen

"Toppers" wrote:

Try:

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

All dates assumed to be DATE format cells.

"Ellen G" wrote:

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

Glad you fixed the problem.

Ellen G wrote:

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" wrote:

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.

Ellen G wrote:

We're getting closer. My formula now looks like this:

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

I'm no longer getting an error, but I'm not getting a sum of row 7. I'm
ending up with 0. I have double checked to make sure I have values in row 7
within the campaign range -- I do indeed. So any thoughts?

Thanks so much.

Ellen

"Toppers" wrote:

Try:

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

All dates assumed to be DATE format cells.

"Ellen G" wrote:

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


--

Dave Peterson