You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Carrach" wrote in message
...
YES YES YES - Thank you so much for all your help it all works
beautifully.
--
thanks
carrach


"T. Valko" wrote:

For a count just drop the last array (the sum array Sheet1!F2:F100):

=SUMPRODUCT(--(Sheet1!A2:A100&"
"&Sheet1!B2:B100=A2),--(MONTH(Sheet1!D2:D100)=4))

Whe

A2 = Fred Smith
Sheet1!A2:A100 = first names
Sheet1!B2:B100 = last names

--
Biff
Microsoft Excel MVP


"Carrach" wrote in message
...
Hi Biff,
Looks as if it might work however I have just realised that for this
particular sheet, instead of adding a value in column F I need to count
the
number of rows in sheet 1 that are in that particular month. Do I
still
use
SUMPRODUCT or should I in fact be using something else.
sorry

--
any help gratefully received
thanks
carrach


"T. Valko" wrote:

Try something like this...

A2 = Fred Smith

column C = first names
column D = last names

=SUMPRODUCT(--(C2:C5&" "&D2:D5=A2).....

--
Biff
Microsoft Excel MVP


"Carrach" wrote in message
...
Assume your names in Sheet1 are in column A, the dates are in column
D, and the values you want to add are in column F. Further assume
that
the target_name in this_sheet is in A2. Try this formula in a cell
in
this_sheet:
=SUMPRODUCT(--(Sheet1!A2:A100=A2),--(MONTH(Sheet1!D2:D100)=4),Sheet1!
F2:F100)
This formulae works very well (thanks to Pete for his help), however
I
need
to use the same formulae to match the name in A2 to a spreadsheet
that
has
the name to be matched to in two columns (first name (col A), last
name
(Col
B).
I currently use the following to match names in this manner but dont
know
how to use it in the formulae above:
=SUMPRODUCT(--(sa_advisor_LAST_NAME="Smith"),--(sa_advisor_FIRST_NAME="Fred"))
Can anyone help please?
regards
Carrach




.



.