| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
| SumProduct | Excel Discussion (Misc queries) | |||
| HELP ON SUMPRODUCT() | Excel Worksheet Functions | |||
| sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
| Sumproduct..help please | Excel Worksheet Functions | |||
Linear Mode
