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.

"Mark R" wrote:

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.

"Toppers" wrote:

==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


"Mark R" wrote:

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.