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

???

"Mark R" wrote:

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


"T. Valko" wrote:

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="Abbe y"))

--
Biff
Microsoft Excel MVP


"Mark R" wrote in message
...
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.