SumProduct Help
 

Hi All,
I'm trying to find the number of dates that fall between 7/18/08 and 7/25/08
in the follwoing table. I'm using sumproduct with very little luck. I've
tried the following formulas:

SUMPRODUCT((A1:A10=7/18/2008)+(A1:A10=7/25/2008)) yields 10 and
SUMPRODUCT((A1:A10=7/18/2008)*(A1:A10=7/25/2008)) yields 0

What I'm looking for is 7. Maybe I'm using the wrong formula. Any help
would be greatly appreciated.

7/18/2008
7/19/2008
7/20/2008
7/26/2008
7/24/2008
7/27/2008
7/25/2008
7/18/2008
7/18/2008
7/3/2008

Thanx,
John

SumProduct Help
 

Try,

=SUMPRODUCT((A1:A10=DATE(2008,7,18))*(A1:A10<=DAT E(2008,7,25)))

Mike

"JCS" wrote:

Hi All,
I'm trying to find the number of dates that fall between 7/18/08 and 7/25/08
in the follwoing table. I'm using sumproduct with very little luck. I've
tried the following formulas:

SUMPRODUCT((A1:A10=7/18/2008)+(A1:A10=7/25/2008)) yields 10 and
SUMPRODUCT((A1:A10=7/18/2008)*(A1:A10=7/25/2008)) yields 0

What I'm looking for is 7. Maybe I'm using the wrong formula. Any help
would be greatly appreciated.

7/18/2008
7/19/2008
7/20/2008
7/26/2008
7/24/2008
7/27/2008
7/25/2008
7/18/2008
7/18/2008
7/3/2008

Thanx,
John

SumProduct Help
 

Try this instead:

SUMPRODUCT((A1:A10=--"7/18/2008")*(A1:A10<=--"7/25/2008"))

or you could try it this way:

=SUMPRODUCT((A1:A10=DATE(2008,7,18))*(A1:A10<=DAT E(2008,7,25)))

Hope this helps.

Pete

On Aug 19, 3:31*pm, JCS wrote:
Hi All,
I'm trying to find the number of dates that fall between 7/18/08 and 7/25/08
in the follwoing table. *I'm using sumproduct with very little luck. *I've
tried the following formulas:

SUMPRODUCT((A1:A10=7/18/2008)+(A1:A10=7/25/2008)) yields 10 and
SUMPRODUCT((A1:A10=7/18/2008)*(A1:A10=7/25/2008)) yields 0

What I'm looking for is 7. *Maybe I'm using the wrong formula. *Any help
would be greatly appreciated.

7/18/2008 * *
7/19/2008
7/20/2008 * *
7/26/2008 * *
7/24/2008 * *
7/27/2008 * * *
7/25/2008 * *
7/18/2008 * *
7/18/2008 * *
7/3/2008 * * *

Thanx,
John

SumProduct Help
 

Both suggestions are excellent but if I have a table that looks like the
following:
7/18/2008
7/18/2008
7/18/2008
7/18/2008
7/18/2008
7/18/2008
7/18/2008
7/18/2008
7/18/2008
7/3/2008

Both fiormulas yield 8 instead of nine. Any suggestions?

Thank you,
John

"JCS" wrote:

Hi All,
I'm trying to find the number of dates that fall between 7/18/08 and 7/25/08
in the follwoing table. I'm using sumproduct with very little luck. I've
tried the following formulas:

SUMPRODUCT((A1:A10=7/18/2008)+(A1:A10=7/25/2008)) yields 10 and
SUMPRODUCT((A1:A10=7/18/2008)*(A1:A10=7/25/2008)) yields 0

What I'm looking for is 7. Maybe I'm using the wrong formula. Any help
would be greatly appreciated.

7/18/2008
7/19/2008
7/20/2008
7/26/2008
7/24/2008
7/27/2008
7/25/2008
7/18/2008
7/18/2008
7/3/2008

Thanx,
John

SumProduct Help
 

Just check that the formula has the same range (twice) as your table
uses. If they are the same then check on your data - for some reason
there is a space before the final 7/18/2008 in your posting, so if you
have copied that directly from your file then perhaps that cell
contains a text value rather than a true date.

Hope this helps.

Pete

On Aug 19, 4:52*pm, JCS wrote:
Both suggestions are excellent but if I have a table that looks like the
following:
7/18/2008
7/18/2008
7/18/2008
7/18/2008
7/18/2008
7/18/2008
7/18/2008
7/18/2008
*7/18/2008 * *
7/3/2008

Both fiormulas yield 8 instead of nine. *Any suggestions?

Thank you,
John



"JCS" wrote:
Hi All,
I'm trying to find the number of dates that fall between 7/18/08 and 7/25/08
in the follwoing table. *I'm using sumproduct with very little luck. *I've
tried the following formulas:

SUMPRODUCT((A1:A10=7/18/2008)+(A1:A10=7/25/2008)) yields 10 and
SUMPRODUCT((A1:A10=7/18/2008)*(A1:A10=7/25/2008)) yields 0

What I'm looking for is 7. *Maybe I'm using the wrong formula. *Any help
would be greatly appreciated.

7/18/2008 * *
7/19/2008
7/20/2008 * *
7/26/2008 * *
7/24/2008 * *
7/27/2008 * * *
7/25/2008 * *
7/18/2008 * *
7/18/2008 * *
7/3/2008 * * *

Thanx,
John- Hide quoted text -

- Show quoted text -

SumProduct Help
 

You only have 8 dates that satisfy your criteria. Of your 10 cells, one
cell has a date outside your specified criteria, and another apparently has
text, rather than a date.
--
David Biddulph

"JCS" wrote in message
...
Both suggestions are excellent but if I have a table that looks like the
following:
7/18/2008
7/18/2008
7/18/2008
7/18/2008
7/18/2008
7/18/2008
7/18/2008
7/18/2008
7/18/2008
7/3/2008

Both fiormulas yield 8 instead of nine. Any suggestions?

Thank you,
John

"JCS" wrote:

Hi All,
I'm trying to find the number of dates that fall between 7/18/08 and
7/25/08
in the follwoing table. I'm using sumproduct with very little luck.
I've
tried the following formulas:

SUMPRODUCT((A1:A10=7/18/2008)+(A1:A10=7/25/2008)) yields 10 and
SUMPRODUCT((A1:A10=7/18/2008)*(A1:A10=7/25/2008)) yields 0

What I'm looking for is 7. Maybe I'm using the wrong formula. Any help
would be greatly appreciated.

7/18/2008
7/19/2008
7/20/2008
7/26/2008
7/24/2008
7/27/2008
7/25/2008
7/18/2008
7/18/2008
7/3/2008

Thanx,
John

SumProduct Help
 

Peter,
I took out the space as you suggested but get the same result. Is there a
betterway to do this?

Many thanks,
John

"Pete_UK" wrote:

Just check that the formula has the same range (twice) as your table
uses. If they are the same then check on your data - for some reason
there is a space before the final 7/18/2008 in your posting, so if you
have copied that directly from your file then perhaps that cell
contains a text value rather than a true date.

Hope this helps.

Pete

On Aug 19, 4:52 pm, JCS wrote:
Both suggestions are excellent but if I have a table that looks like the
following:
7/18/2008
7/18/2008
7/18/2008
7/18/2008
7/18/2008
7/18/2008
7/18/2008
7/18/2008
7/18/2008
7/3/2008

Both fiormulas yield 8 instead of nine. Any suggestions?

Thank you,
John



"JCS" wrote:
Hi All,
I'm trying to find the number of dates that fall between 7/18/08 and 7/25/08
in the follwoing table. I'm using sumproduct with very little luck. I've
tried the following formulas:

SUMPRODUCT((A1:A10=7/18/2008)+(A1:A10=7/25/2008)) yields 10 and
SUMPRODUCT((A1:A10=7/18/2008)*(A1:A10=7/25/2008)) yields 0

What I'm looking for is 7. Maybe I'm using the wrong formula. Any help
would be greatly appreciated.

7/18/2008
7/19/2008
7/20/2008
7/26/2008
7/24/2008
7/27/2008
7/25/2008
7/18/2008
7/18/2008
7/3/2008

Thanx,
John- Hide quoted text -

- Show quoted text -

SumProduct Help
 

It's no good just taking out the space, because you have presumably still
got text instead of a date. Check what you get with =ISTEXT(A9) and
=ISNUMBER(A9).
Put in a date in the cell instead of the text, and the formula will work.
--
David Biddulph

"JCS" wrote in message
...
Peter,
I took out the space as you suggested but get the same result. Is there a
betterway to do this?

Many thanks,
John

"Pete_UK" wrote:

Just check that the formula has the same range (twice) as your table
uses. If they are the same then check on your data - for some reason
there is a space before the final 7/18/2008 in your posting, so if you
have copied that directly from your file then perhaps that cell
contains a text value rather than a true date.

Hope this helps.

Pete

On Aug 19, 4:52 pm, JCS wrote:
Both suggestions are excellent but if I have a table that looks like
the
following:
7/18/2008
7/18/2008
7/18/2008
7/18/2008
7/18/2008
7/18/2008
7/18/2008
7/18/2008
7/18/2008
7/3/2008

Both fiormulas yield 8 instead of nine. Any suggestions?

Thank you,
John



"JCS" wrote:
Hi All,
I'm trying to find the number of dates that fall between 7/18/08 and
7/25/08
in the follwoing table. I'm using sumproduct with very little luck.
I've
tried the following formulas:

SUMPRODUCT((A1:A10=7/18/2008)+(A1:A10=7/25/2008)) yields 10 and
SUMPRODUCT((A1:A10=7/18/2008)*(A1:A10=7/25/2008)) yields 0

What I'm looking for is 7. Maybe I'm using the wrong formula. Any
help
would be greatly appreciated.

7/18/2008
7/19/2008
7/20/2008
7/26/2008
7/24/2008
7/27/2008
7/25/2008
7/18/2008
7/18/2008
7/3/2008

Thanx,
John- Hide quoted text -

- Show quoted text -

SumProduct Help
 

Assuming that all 8 dates above that offending one are true dates,
just copy one of them and paste it into A9 - this will overwrite the
text formatting that you seem to have in that cell.

Hope this helps.

Pete

On Aug 19, 5:52*pm, JCS wrote:
Peter,
I took out the space as you suggested but get the same result. *Is there a
betterway to do this?

Many thanks,
John



"Pete_UK" wrote:
Just check that the formula has the same range (twice) as your table
uses. If they are the same then check on your data - for some reason
there is a space before the final 7/18/2008 in your posting, so if you
have copied that directly from your file then perhaps that cell
contains a text value rather than a true date.

Hope this helps.

Pete

On Aug 19, 4:52 pm, JCS wrote:
Both suggestions are excellent but if I have a table that looks like the
following:
7/18/2008
7/18/2008
7/18/2008
7/18/2008
7/18/2008
7/18/2008
7/18/2008
7/18/2008
*7/18/2008 * *
7/3/2008

Both fiormulas yield 8 instead of nine. *Any suggestions?

Thank you,
John

"JCS" wrote:
Hi All,
I'm trying to find the number of dates that fall between 7/18/08 and 7/25/08
in the follwoing table. *I'm using sumproduct with very little luck. *I've
tried the following formulas:

SUMPRODUCT((A1:A10=7/18/2008)+(A1:A10=7/25/2008)) yields 10 and
SUMPRODUCT((A1:A10=7/18/2008)*(A1:A10=7/25/2008)) yields 0

What I'm looking for is 7. *Maybe I'm using the wrong formula. *Any help
would be greatly appreciated.

7/18/2008 * *
7/19/2008
7/20/2008 * *
7/26/2008 * *
7/24/2008 * *
7/27/2008 * * *
7/25/2008 * *
7/18/2008 * *
7/18/2008 * *
7/3/2008 * * *

Thanx,
John- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

SumProduct Help
 

Peter, Mike, and David,

Many thanks for your help! Pete/David, you were right my data was causing
the problem. I re-typed the data and the formula worked perfectly. In fact
both formulas worked great. Again, thank you for your help.
John

"JCS" wrote:

Hi All,
I'm trying to find the number of dates that fall between 7/18/08 and 7/25/08
in the follwoing table. I'm using sumproduct with very little luck. I've
tried the following formulas:

SUMPRODUCT((A1:A10=7/18/2008)+(A1:A10=7/25/2008)) yields 10 and
SUMPRODUCT((A1:A10=7/18/2008)*(A1:A10=7/25/2008)) yields 0

What I'm looking for is 7. Maybe I'm using the wrong formula. Any help
would be greatly appreciated.

7/18/2008
7/19/2008
7/20/2008
7/26/2008
7/24/2008
7/27/2008
7/25/2008
7/18/2008
7/18/2008
7/3/2008

Thanx,
John

SumProduct Help
 

You're welcome, John - glad you got it to work in the end.

Thanks for feeding back.

Pete

On Aug 19, 7:11*pm, JCS wrote:
Peter, Mike, and David,

Many thanks for your help! *Pete/David, you were right my data was causing
the problem. *I re-typed the data and the formula worked perfectly. *In fact
both formulas worked great. *Again, thank you for your help.
John



"JCS" wrote:
Hi All,
I'm trying to find the number of dates that fall between 7/18/08 and 7/25/08
in the follwoing table. *I'm using sumproduct with very little luck. *I've
tried the following formulas:

SUMPRODUCT((A1:A10=7/18/2008)+(A1:A10=7/25/2008)) yields 10 and
SUMPRODUCT((A1:A10=7/18/2008)*(A1:A10=7/25/2008)) yields 0

What I'm looking for is 7. *Maybe I'm using the wrong formula. *Any help
would be greatly appreciated.

7/18/2008 * *
7/19/2008
7/20/2008 * *
7/26/2008 * *
7/24/2008 * *
7/27/2008 * * *
7/25/2008 * *
7/18/2008 * *
7/18/2008 * *
7/3/2008 * * *

Thanx,
John- Hide quoted text -

- Show quoted text -