Roger Govier Wrote:
Hi

My apologies, the formula is missing the number of characters to take,
and
the closing bracket after the left statement.

=SUMPRODUCT(--(LEFT(Sheet1!$A$2:$A$1000,4)="John"))

Regards

Roger Govier


Ltat42a wrote:
Roger Govier Wrote:

Hi

On sheet2
=SUMPRODUCT(--(LEFT(Sheet1!$A$2:$A$1000="John"))
Change sheet name and range to suit

Regards

Roger Govier


Ltat42a wrote:

Expanding on this topic a lil bit. I'm looking to do something very
similar.
I have a list of names(about 20) in column A. In the columns to the
right I have
certain data - some text, some numbers.

What I'm looking to do is count the number of times a person is

listed

(it is possible for these names to repeat during the year) in
column
A,
then all of the data to the right of that person's name?

For instance, I have -
NAME DATE HOURS TEXT TEXT
John Doe 12/13/05 8 Text Text
Jane Doe 12/12/05 4 Text Text
ETC
ETC


John will be listed several times in column A, I'm wanting to find

out

how many times John is listed along with the data in the row.
The results will be on another worksheet.

Is this possible?

Thanx.





Thanx for the reply, I put the above formula on Sheet2, I'm getting
the
#VALUE! error. I'm using Excel from Office XP. I've got all my data
in,
can't figure out why I'm getting the error.

JF



That worked. It now returns the number of times "John" is listed in
column A,
however, it doesn't copy of the data in columns B-E on the row that
contains "John".

Is that still possible?

Thanx...JF


--
Ltat42a
------------------------------------------------------------------------
Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735
View this thread: http://www.excelforum.com/showthread...hreadid=493067