Countind date between two dates  formula results 

Author 
Message 
Sisilla

Posted: Sun Jul 29 12:02:00 CDT 2007 
Top 
Excel Misc >> Countind date between two dates  formula results
I have a spreadsheet that has clients names and then the date that I
submitted the business for them i.e
Mr Jones 12/12/2006 Abbey
Mrs Smith 12/01/2007 Natwest
What is the best way to count all the submitted business in 1 month say for
January only. Also how would i count all the Abbey cases in say 2007, or 2006.
I need this as a formula so that I can populate graphes etc with it.
Excel90





Toppers

Posted: Sun Jul 29 12:02:00 CDT 2007 
Top 
Excel Misc >> Countind date between two dates  formula results
==SUMPRODUCT((B2:B100<>""),(MONTH(B2:B100=1)),(YEAR(B2:B100=2007)))
=SUMPRODUCT((B2:B100<>""),(MONTH(B2:B100=1)),(YEAR(B2:B100=2007)))
=SUMPRODUCT((B2:B100<>""),(C2:C100="Abbey"),(YEAR(B2:B100=2007)))
It's better to put the parameters in cellS:
=SUMPRODUCT((B2:B100<>""),(C2:C100=X1),(YEAR(B2:B100=X2)))
X1="Abbey"
X2=2007
HTH
> I have a spreadsheet that has clients names and then the date that I
> submitted the business for them i.e
>
> Mr Jones 12/12/2006 Abbey
> Mrs Smith 12/01/2007 Natwest
>
> What is the best way to count all the submitted business in 1 month say for
> January only. Also how would i count all the Abbey cases in say 2007, or 2006.
>
> I need this as a formula so that I can populate graphes etc with it.
>





BriSwy

Posted: Sun Jul 29 12:04:01 CDT 2007 
Top 
Excel Misc >> Countind date between two dates  formula results
A rather crude way, yet effective, is to create a formula in column D that
concatenates the year from column B and the client from column C. THe
formula is:
=year(b2)&c2
This would yield "2006Abbey". Then copy that down all rows for your data.
Once complete, in another cell you can enter the formula:
=countif(D:D,"2006Abbey")
Hope this helps!
> I have a spreadsheet that has clients names and then the date that I
> submitted the business for them i.e
>
> Mr Jones 12/12/2006 Abbey
> Mrs Smith 12/01/2007 Natwest
>
> What is the best way to count all the submitted business in 1 month say for
> January only. Also how would i count all the Abbey cases in say 2007, or 2006.
>
> I need this as a formula so that I can populate graphes etc with it.
>





MarkR

Posted: Sun Jul 29 12:22:01 CDT 2007 
Top 
Excel Misc >> Countind date between two dates  formula results
Hi Toppers,
I have used the bottom formula and thats almost perfect, except the cell has
the complete date in it, so 2007 is only part of the cell, i.e my cell is
01/03/2007 so I need to pick the year only part of the date.
> ==SUMPRODUCT((B2:B100<>""),(MONTH(B2:B100=1)),(YEAR(B2:B100=2007)))
>
> =SUMPRODUCT((B2:B100<>""),(MONTH(B2:B100=1)),(YEAR(B2:B100=2007)))
>
> =SUMPRODUCT((B2:B100<>""),(C2:C100="Abbey"),(YEAR(B2:B100=2007)))
>
> It's better to put the parameters in cellS:
>
> =SUMPRODUCT((B2:B100<>""),(C2:C100=X1),(YEAR(B2:B100=X2)))
>
> X1="Abbey"
> X2=2007
>
> HTH
>
>
>
> > I have a spreadsheet that has clients names and then the date that I
> > submitted the business for them i.e
> >
> > Mr Jones 12/12/2006 Abbey
> > Mrs Smith 12/01/2007 Natwest
> >
> > What is the best way to count all the submitted business in 1 month say for
> > January only. Also how would i count all the Abbey cases in say 2007, or 2006.
> >
> > I need this as a formula so that I can populate graphes etc with it.
> >





T

Posted: Sun Jul 29 12:43:42 CDT 2007 
Top 
Excel Misc >> Countind date between two dates  formula results
To count entries for the month of *January*. This will include *all* years.
=SUMPRODUCT((ISNUMBER(B1:B9)),(MONTH(B1:B9)=1))
To count entries for *any other month*:
=SUMPRODUCT((MONTH(B1:B9)=month_number))
To count for January of a specific year (if you might be counting for
January 1900 include the ISNUMBER test. Empty cells evaluate as date January
0 1900):
=SUMPRODUCT((MONTH(B1:B9)=1),(YEAR(B1:B9)=year_number))
Or:
=SUMPRODUCT((TEXT(B1:B9,"mmmyyyy")="Jan2007"))
To count entries of "Abbey" for years 2006, 2007 (includes *all* months):
=SUMPRODUCT((YEAR(B1:B9)={2006,2007})*(C1:C9="Abbey"))

Biff
Microsoft Excel MVP
>I have a spreadsheet that has clients names and then the date that I
> submitted the business for them i.e
>
> Mr Jones 12/12/2006 Abbey
> Mrs Smith 12/01/2007 Natwest
>
> What is the best way to count all the submitted business in 1 month say
> for
> January only. Also how would i count all the Abbey cases in say 2007, or
> 2006.
>
> I need this as a formula so that I can populate graphes etc with it.
>





BriSwy

Posted: Sun Jul 29 12:48:04 CDT 2007 
Top 
Excel Misc >> Countind date between two dates  formula results
That formula will still work Mark. He omitted a ")" in it though after the
2nd B100 reference. The correct formula should read:
=SUMPRODUCT((B2:B100<>""),(C2:C100=X1),(YEAR(B2:B100)=X2)))
The YEAR function only looks at the YEAR in the full date, so that is what
you need.
> Hi Toppers,
>
> I have used the bottom formula and thats almost perfect, except the cell has
> the complete date in it, so 2007 is only part of the cell, i.e my cell is
> 01/03/2007 so I need to pick the year only part of the date.
>
>
> > ==SUMPRODUCT((B2:B100<>""),(MONTH(B2:B100=1)),(YEAR(B2:B100=2007)))
> >
> > =SUMPRODUCT((B2:B100<>""),(MONTH(B2:B100=1)),(YEAR(B2:B100=2007)))
> >
> > =SUMPRODUCT((B2:B100<>""),(C2:C100="Abbey"),(YEAR(B2:B100=2007)))
> >
> > It's better to put the parameters in cellS:
> >
> > =SUMPRODUCT((B2:B100<>""),(C2:C100=X1),(YEAR(B2:B100=X2)))
> >
> > X1="Abbey"
> > X2=2007
> >
> > HTH
> >
> >
> >
> > > I have a spreadsheet that has clients names and then the date that I
> > > submitted the business for them i.e
> > >
> > > Mr Jones 12/12/2006 Abbey
> > > Mrs Smith 12/01/2007 Natwest
> > >
> > > What is the best way to count all the submitted business in 1 month say for
> > > January only. Also how would i count all the Abbey cases in say 2007, or 2006.
> > >
> > > I need this as a formula so that I can populate graphes etc with it.
> > >





Toppers

Posted: Sun Jul 29 12:52:01 CDT 2007 
Top 
Excel Misc >> Countind date between two dates  formula results
That's what the formula does ... selects year=2007 from a date field.
Did you try it?
> Hi Toppers,
>
> I have used the bottom formula and thats almost perfect, except the cell has
> the complete date in it, so 2007 is only part of the cell, i.e my cell is
> 01/03/2007 so I need to pick the year only part of the date.
>
>
> > ==SUMPRODUCT((B2:B100<>""),(MONTH(B2:B100=1)),(YEAR(B2:B100=2007)))
> >
> > =SUMPRODUCT((B2:B100<>""),(MONTH(B2:B100=1)),(YEAR(B2:B100=2007)))
> >
> > =SUMPRODUCT((B2:B100<>""),(C2:C100="Abbey"),(YEAR(B2:B100=2007)))
> >
> > It's better to put the parameters in cellS:
> >
> > =SUMPRODUCT((B2:B100<>""),(C2:C100=X1),(YEAR(B2:B100=X2)))
> >
> > X1="Abbey"
> > X2=2007
> >
> > HTH
> >
> >
> >
> > > I have a spreadsheet that has clients names and then the date that I
> > > submitted the business for them i.e
> > >
> > > Mr Jones 12/12/2006 Abbey
> > > Mrs Smith 12/01/2007 Natwest
> > >
> > > What is the best way to count all the submitted business in 1 month say for
> > > January only. Also how would i count all the Abbey cases in say 2007, or 2006.
> > >
> > > I need this as a formula so that I can populate graphes etc with it.
> > >





MarkR

Posted: Sun Jul 29 13:04:03 CDT 2007 
Top 
Excel Misc >> Countind date between two dates  formula results
I have tried this toppers, it comes back with the result FALSE in the cell,
this is the formula
=SUMPRODUCT((E25:E28<>""),(G25:G28="Abbey"),(YEAR(F25:F28=2005)))
and the data
E F G
Dave 01/03/2005 Abbey
Gary 01/03/2006 Abbey
Jane 02/03/2005 Abbey
Gaz 05/03/2005 Abbey
I think this should return the result in my cell of 3
> That's what the formula does ... selects year=2007 from a date field.
>
> Did you try it?
>
>
> > Hi Toppers,
> >
> > I have used the bottom formula and thats almost perfect, except the cell has
> > the complete date in it, so 2007 is only part of the cell, i.e my cell is
> > 01/03/2007 so I need to pick the year only part of the date.
> >
> >
> > > ==SUMPRODUCT((B2:B100<>""),(MONTH(B2:B100=1)),(YEAR(B2:B100=2007)))
> > >
> > > =SUMPRODUCT((B2:B100<>""),(MONTH(B2:B100=1)),(YEAR(B2:B100=2007)))
> > >
> > > =SUMPRODUCT((B2:B100<>""),(C2:C100="Abbey"),(YEAR(B2:B100=2007)))
> > >
> > > It's better to put the parameters in cellS:
> > >
> > > =SUMPRODUCT((B2:B100<>""),(C2:C100=X1),(YEAR(B2:B100=X2)))
> > >
> > > X1="Abbey"
> > > X2=2007
> > >
> > > HTH
> > >
> > >
> > >
> > > > I have a spreadsheet that has clients names and then the date that I
> > > > submitted the business for them i.e
> > > >
> > > > Mr Jones 12/12/2006 Abbey
> > > > Mrs Smith 12/01/2007 Natwest
> > > >
> > > > What is the best way to count all the submitted business in 1 month say for
> > > > January only. Also how would i count all the Abbey cases in say 2007, or 2006.
> > > >
> > > > I need this as a formula so that I can populate graphes etc with it.
> > > >





MarkR

Posted: Sun Jul 29 13:20:02 CDT 2007 
Top 
Excel Misc >> Countind date between two dates  formula results
Biff, you the man. thanks a lot  thats solved my headache
> To count entries for the month of *January*. This will include *all* years.
>
> =SUMPRODUCT((ISNUMBER(B1:B9)),(MONTH(B1:B9)=1))
>
> To count entries for *any other month*:
>
> =SUMPRODUCT((MONTH(B1:B9)=month_number))
>
> To count for January of a specific year (if you might be counting for
> January 1900 include the ISNUMBER test. Empty cells evaluate as date January
> 0 1900):
>
> =SUMPRODUCT((MONTH(B1:B9)=1),(YEAR(B1:B9)=year_number))
>
> Or:
>
> =SUMPRODUCT((TEXT(B1:B9,"mmmyyyy")="Jan2007"))
>
> To count entries of "Abbey" for years 2006, 2007 (includes *all* months):
>
> =SUMPRODUCT((YEAR(B1:B9)={2006,2007})*(C1:C9="Abbey"))
>
> 
> Biff
> Microsoft Excel MVP
>
>
> >I have a spreadsheet that has clients names and then the date that I
> > submitted the business for them i.e
> >
> > Mr Jones 12/12/2006 Abbey
> > Mrs Smith 12/01/2007 Natwest
> >
> > What is the best way to count all the submitted business in 1 month say
> > for
> > January only. Also how would i count all the Abbey cases in say 2007, or
> > 2006.
> >
> > I need this as a formula so that I can populate graphes etc with it.
> >
>
>
>





T

Posted: Sun Jul 29 13:22:10 CDT 2007 
Top 
Excel Misc >> Countind date between two dates  formula results
>=SUMPRODUCT((E25:E28<>""),(G25:G28="Abbey"),(YEAR(F25:F28=2005)))
Try it like this:
=SUMPRODUCT((E25:E28<>""),(G25:G28="Abbey"),(YEAR(F25:F28)=2005))

Biff
Microsoft Excel MVP
>I have tried this toppers, it comes back with the result FALSE in the cell,
> this is the formula
> =SUMPRODUCT((E25:E28<>""),(G25:G28="Abbey"),(YEAR(F25:F28=2005)))
>
> and the data
>
> E F G
> Dave 01/03/2005 Abbey
> Gary 01/03/2006 Abbey
> Jane 02/03/2005 Abbey
> Gaz 05/03/2005 Abbey
>
> I think this should return the result in my cell of 3
>
>
>
>> That's what the formula does ... selects year=2007 from a date field.
>>
>> Did you try it?
>>
>>
>> > Hi Toppers,
>> >
>> > I have used the bottom formula and thats almost perfect, except the
>> > cell has
>> > the complete date in it, so 2007 is only part of the cell, i.e my cell
>> > is
>> > 01/03/2007 so I need to pick the year only part of the date.
>> >
>> >
>> > > ==SUMPRODUCT((B2:B100<>""),(MONTH(B2:B100=1)),(YEAR(B2:B100=2007)))
>> > >
>> > > =SUMPRODUCT((B2:B100<>""),(MONTH(B2:B100=1)),(YEAR(B2:B100=2007)))
>> > >
>> > > =SUMPRODUCT((B2:B100<>""),(C2:C100="Abbey"),(YEAR(B2:B100=2007)))
>> > >
>> > > It's better to put the parameters in cellS:
>> > >
>> > > =SUMPRODUCT((B2:B100<>""),(C2:C100=X1),(YEAR(B2:B100=X2)))
>> > >
>> > > X1="Abbey"
>> > > X2=2007
>> > >
>> > > HTH
>> > >
>> > >
>> > >
>> > > > I have a spreadsheet that has clients names and then the date that
>> > > > I
>> > > > submitted the business for them i.e
>> > > >
>> > > > Mr Jones 12/12/2006 Abbey
>> > > > Mrs Smith 12/01/2007 Natwest
>> > > >
>> > > > What is the best way to count all the submitted business in 1 month
>> > > > say for
>> > > > January only. Also how would i count all the Abbey cases in say
>> > > > 2007, or 2006.
>> > > >
>> > > > I need this as a formula so that I can populate graphes etc with
>> > > > it.
>> > > >





Toppers

Posted: Sun Jul 29 13:30:01 CDT 2007 
Top 
Excel Misc >> Countind date between two dates  formula results
Mark,
Mea culpa ... I missed off a bracket. See Biff's reply
> I have tried this toppers, it comes back with the result FALSE in the cell,
> this is the formula
> =SUMPRODUCT((E25:E28<>""),(G25:G28="Abbey"),(YEAR(F25:F28=2005)))
>
> and the data
>
> E F G
> Dave 01/03/2005 Abbey
> Gary 01/03/2006 Abbey
> Jane 02/03/2005 Abbey
> Gaz 05/03/2005 Abbey
>
> I think this should return the result in my cell of 3
>
>
>
> > That's what the formula does ... selects year=2007 from a date field.
> >
> > Did you try it?
> >
> >
> > > Hi Toppers,
> > >
> > > I have used the bottom formula and thats almost perfect, except the cell has
> > > the complete date in it, so 2007 is only part of the cell, i.e my cell is
> > > 01/03/2007 so I need to pick the year only part of the date.
> > >
> > >
> > > > ==SUMPRODUCT((B2:B100<>""),(MONTH(B2:B100=1)),(YEAR(B2:B100=2007)))
> > > >
> > > > =SUMPRODUCT((B2:B100<>""),(MONTH(B2:B100=1)),(YEAR(B2:B100=2007)))
> > > >
> > > > =SUMPRODUCT((B2:B100<>""),(C2:C100="Abbey"),(YEAR(B2:B100=2007)))
> > > >
> > > > It's better to put the parameters in cellS:
> > > >
> > > > =SUMPRODUCT((B2:B100<>""),(C2:C100=X1),(YEAR(B2:B100=X2)))
> > > >
> > > > X1="Abbey"
> > > > X2=2007
> > > >
> > > > HTH
> > > >
> > > >
> > > >
> > > > > I have a spreadsheet that has clients names and then the date that I
> > > > > submitted the business for them i.e
> > > > >
> > > > > Mr Jones 12/12/2006 Abbey
> > > > > Mrs Smith 12/01/2007 Natwest
> > > > >
> > > > > What is the best way to count all the submitted business in 1 month say for
> > > > > January only. Also how would i count all the Abbey cases in say 2007, or 2006.
> > > > >
> > > > > I need this as a formula so that I can populate graphes etc with it.
> > > > >





MarkR

Posted: Sun Jul 29 14:16:01 CDT 2007 
Top 
Excel Misc >> Countind date between two dates  formula results
oh my god, it works brilliantly if i am on the same sheet, but i want to look
at data from another worksheet, just a tab along, I have replace the
appropriate date but now it doesnt like the formula. This is what I have at
the mo(Mortgages being another worksheet but int the same book). It is the
same formula that works but merely grabbing data elsewhere????????????
=SUMPRODUCT((Mortgages!C176:C186<>""),(Mortgages!F174:F187="Abbey"),(YEAR(Mortgages!K174:K185)=2007))
C = the name of client
F = the company
K = The date submitted
aaaaaaaaaaaaaaaaaaaaargh
> To count entries for the month of *January*. This will include *all* years.
>
> =SUMPRODUCT((ISNUMBER(B1:B9)),(MONTH(B1:B9)=1))
>
> To count entries for *any other month*:
>
> =SUMPRODUCT((MONTH(B1:B9)=month_number))
>
> To count for January of a specific year (if you might be counting for
> January 1900 include the ISNUMBER test. Empty cells evaluate as date January
> 0 1900):
>
> =SUMPRODUCT((MONTH(B1:B9)=1),(YEAR(B1:B9)=year_number))
>
> Or:
>
> =SUMPRODUCT((TEXT(B1:B9,"mmmyyyy")="Jan2007"))
>
> To count entries of "Abbey" for years 2006, 2007 (includes *all* months):
>
> =SUMPRODUCT((YEAR(B1:B9)={2006,2007})*(C1:C9="Abbey"))
>
> 
> Biff
> Microsoft Excel MVP
>
>
> >I have a spreadsheet that has clients names and then the date that I
> > submitted the business for them i.e
> >
> > Mr Jones 12/12/2006 Abbey
> > Mrs Smith 12/01/2007 Natwest
> >
> > What is the best way to count all the submitted business in 1 month say
> > for
> > January only. Also how would i count all the Abbey cases in say 2007, or
> > 2006.
> >
> > I need this as a formula so that I can populate graphes etc with it.
> >
>
>
>





Toppers

Posted: Sun Jul 29 14:24:01 CDT 2007 
Top 
Excel Misc >> Countind date between two dates  formula results
The array (range) sizes must be the same size when using SUMPRODUCT.
=SUMPRODUCT((Mortgages!C174:C187<>""),(Mortgages!F174:F187="Abbey"),(YEAR(Mortgages!K174:K187)=2007))
???
> oh my god, it works brilliantly if i am on the same sheet, but i want to look
> at data from another worksheet, just a tab along, I have replace the
> appropriate date but now it doesnt like the formula. This is what I have at
> the mo(Mortgages being another worksheet but int the same book). It is the
> same formula that works but merely grabbing data elsewhere????????????
>
> =SUMPRODUCT((Mortgages!C176:C186<>""),(Mortgages!F174:F187="Abbey"),(YEAR(Mortgages!K174:K185)=2007))
>
> C = the name of client
> F = the company
> K = The date submitted
>
>
> aaaaaaaaaaaaaaaaaaaaargh
>
>
>
> > To count entries for the month of *January*. This will include *all* years.
> >
> > =SUMPRODUCT((ISNUMBER(B1:B9)),(MONTH(B1:B9)=1))
> >
> > To count entries for *any other month*:
> >
> > =SUMPRODUCT((MONTH(B1:B9)=month_number))
> >
> > To count for January of a specific year (if you might be counting for
> > January 1900 include the ISNUMBER test. Empty cells evaluate as date January
> > 0 1900):
> >
> > =SUMPRODUCT((MONTH(B1:B9)=1),(YEAR(B1:B9)=year_number))
> >
> > Or:
> >
> > =SUMPRODUCT((TEXT(B1:B9,"mmmyyyy")="Jan2007"))
> >
> > To count entries of "Abbey" for years 2006, 2007 (includes *all* months):
> >
> > =SUMPRODUCT((YEAR(B1:B9)={2006,2007})*(C1:C9="Abbey"))
> >
> > 
> > Biff
> > Microsoft Excel MVP
> >
> >
> > >I have a spreadsheet that has clients names and then the date that I
> > > submitted the business for them i.e
> > >
> > > Mr Jones 12/12/2006 Abbey
> > > Mrs Smith 12/01/2007 Natwest
> > >
> > > What is the best way to count all the submitted business in 1 month say
> > > for
> > > January only. Also how would i count all the Abbey cases in say 2007, or
> > > 2006.
> > >
> > > I need this as a formula so that I can populate graphes etc with it.
> > >
> >
> >
> >






