Is there a way to retrieve the text content of a cell in the same way that a
SUMPRODUCT function will retrieve numeric content? If not is it possible to
retrieve the ROW number of the cell in a SUMPRODUCT (?) in order to use it in
an INDIRECT function?

I need to retrieve content from a cell (both text and numeric) according to
a combination of values (criteria) in other cells in the same row.

use sumproduct(--(range="text")) or

sumproduct(--(range={"text",value}))

hth
Regards from Brazil
Marcelo

"DoctorG" escreveu:

Is there a way to retrieve the text content of a cell in the same way that a
SUMPRODUCT function will retrieve numeric content? If not is it possible to
retrieve the ROW number of the cell in a SUMPRODUCT (?) in order to use it in
an INDIRECT function?

I need to retrieve content from a cell (both text and numeric) according to
a combination of values (criteria) in other cells in the same row.

Marcelo first of all thanks for your reply.

I must clarify that I want to retrieve the text value not use it as an
argument. My table is a chronological list like :

Who FromDate ToDate Department Salary
---------------------------------------------------------
George 04/12/05 09/30/05 Sales $1000
George 10/01/05 06/25/06 Marketing $1300
Marcelo 05/08/05 05/14/06 Accounting $1500

I need to retrieve the Department and the Salary based on a name and a date,
as in "Which Department was George in on Jan.7 2006 and what was his salary?"

My SUMPRODUCT with Name = George, FromDate <= 01/07/2006, ToDate =
01/07/2006 works well for the salary, because it is a numeric field, but does
not work for the Department because the contents of Column D are text values
and not numbers.

How can I retrieve the contents of text cells using multiple criteria - that
is the question...

By the way... What do the two dashes represent in " --(range="text")", what
is their purpose?

"Marcelo" wrote:

use sumproduct(--(range="text")) or

sumproduct(--(range={"text",value}))

hth
Regards from Brazil
Marcelo

"DoctorG" escreveu:

Is there a way to retrieve the text content of a cell in the same way that a
SUMPRODUCT function will retrieve numeric content? If not is it possible to
retrieve the ROW number of the cell in a SUMPRODUCT (?) in order to use it in
an INDIRECT function?

I need to retrieve content from a cell (both text and numeric) according to
a combination of values (criteria) in other cells in the same row.