Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT within set dates
Hello,
I have some data that I need to count based on two criteria. The tab name for the data is "nd". The first two characters of column B shows the market ID. I then need it to go to column EC and count what dates are in october, november, december and 2010 and beyond. This is the formula I've been using, but I can't get the date format correct: =SUMPRODUCT(-(LEFT(nd!$B:$B,2)="BD"),-(nd!$EC:$EC=10/1/2009<10/31/2009)) Can someone tell me how I should be entering in the date? Thanks, Tom |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT within set dates
Tom,
1 way =SUMPRODUCT((LEFT(ND!$B1:$B5,2)="BD")*(ND!$EC1:$EC 5=DATE(2009,10,1)*(ND!$EC1:$EC5<DATE(2009,10,31)) )) Mike "Tom" wrote: Hello, I have some data that I need to count based on two criteria. The tab name for the data is "nd". The first two characters of column B shows the market ID. I then need it to go to column EC and count what dates are in october, november, december and 2010 and beyond. This is the formula I've been using, but I can't get the date format correct: =SUMPRODUCT(-(LEFT(nd!$B:$B,2)="BD"),-(nd!$EC:$EC=10/1/2009<10/31/2009)) Can someone tell me how I should be entering in the date? Thanks, Tom |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT within set dates
count what dates are in october, november,
december and 2010 and beyond. =SUMPRODUCT(-(LEFT(nd!$B:$B,2)="BD"),-(nd!$EC:$EC=10/1/2009<10/31/2009)) Your formula doesn't match your explanation. Your formula is attempting to only count for the month of OCT 2009 yet your explanation says you want to count from OCT 2009 going forward. To count from OCT 2009 going forward: Try this: =SUMPRODUCT(--(LEFT(nd!$B:$B,2)="BD"),--(nd!$EC:$EC=DATE(2009,10,1))) To count only for OCT 2009: =SUMPRODUCT(--(LEFT(nd!$B:$B,2)="BD"),--(TEXT(nd!$EC:$EC,"mmyyyy")="102009")) I assume you're using Excel 2007 in order to reference the entire columns? -- Biff Microsoft Excel MVP "Tom" wrote in message ... Hello, I have some data that I need to count based on two criteria. The tab name for the data is "nd". The first two characters of column B shows the market ID. I then need it to go to column EC and count what dates are in october, november, december and 2010 and beyond. This is the formula I've been using, but I can't get the date format correct: =SUMPRODUCT(-(LEFT(nd!$B:$B,2)="BD"),-(nd!$EC:$EC=10/1/2009<10/31/2009)) Can someone tell me how I should be entering in the date? Thanks, Tom |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT within set dates
I shortened the ranges for testing, you will need to set them back to what
you need "Mike H" wrote: Tom, 1 way =SUMPRODUCT((LEFT(ND!$B1:$B5,2)="BD")*(ND!$EC1:$EC 5=DATE(2009,10,1)*(ND!$EC1:$EC5<DATE(2009,10,31)) )) Mike "Tom" wrote: Hello, I have some data that I need to count based on two criteria. The tab name for the data is "nd". The first two characters of column B shows the market ID. I then need it to go to column EC and count what dates are in october, november, december and 2010 and beyond. This is the formula I've been using, but I can't get the date format correct: =SUMPRODUCT(-(LEFT(nd!$B:$B,2)="BD"),-(nd!$EC:$EC=10/1/2009<10/31/2009)) Can someone tell me how I should be entering in the date? Thanks, Tom |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT within set dates
Try
=SUMPRODUCT(--(LEFT(nd!$B1:$B10000,2)="BD"),--(nd!$EC1:$EC10000=DATE(2009,10,1)),--(nd!$EC1:$EC10000<DATE(2009,10,31))) I assume you are using 2007 since you are using B:B but I would refrain from using that for 2 reasons, your workbook will become very slow and if you ever save this in 97-2003 format the formula will return a NUM error when someone with <=2003 opens it -- Regards, Peo Sjoblom "Tom" wrote in message ... Hello, I have some data that I need to count based on two criteria. The tab name for the data is "nd". The first two characters of column B shows the market ID. I then need it to go to column EC and count what dates are in october, november, december and 2010 and beyond. This is the formula I've been using, but I can't get the date format correct: =SUMPRODUCT(-(LEFT(nd!$B:$B,2)="BD"),-(nd!$EC:$EC=10/1/2009<10/31/2009)) Can someone tell me how I should be entering in the date? Thanks, Tom |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT and dates | Excel Discussion (Misc queries) | |||
sumproduct with dates | New Users to Excel | |||
SUMPRODUCT - DIFFERENT DATES | Excel Worksheet Functions | |||
Sumproduct and dates | New Users to Excel | |||
SUMPRODUCT and Dates | Excel Worksheet Functions |