returning text; alternative to sumproduct/sumif?
 

I need to check a range and identify each situation where two cells match my
criteria, and in those situations, return the text value of one of those
cells.

My attempts so far which include sumproduct, match, sumif, etc don't seem to
work; sumproduct and sumif seem to want numeric values, match isn't working
because I have to use a wildcard character and the first match is not always
the right one, and I haven't found anything else that will evaluate multiple
cells and return a text value only when both cells meet my criteria.

Are there any other formulas I should play with? I'm reluctant to use array
formulas because this formula will include sumproduct and other formulas, and
my initial testing wasn't fruitful, and I was thinking it might have to do
with entering things like a sumproduct Within an array formula.

=Count(And(criteria1, criteria2))
does provide the expected result (1 match); I just can't figure out how to
tell which column it is in, and return the text from one of my target cells.

I welcome any advice-

Thank you,
Keith

returning text; alternative to sumproduct/sumif?
 

SUMPRODUCT() actually picks the total. If you only have a uniqe record
matching the 2 criterias it works good or otherwise will return the total of
the duplicate records matching both criterias..............Array formula
solution

=INDEX(col3,MATCH(1,(col1=criteria1)*(col2=criteri a2),0))

If this post helps click Yes
---------------
Jacob Skaria


"ker_01" wrote:

I need to check a range and identify each situation where two cells match my
criteria, and in those situations, return the text value of one of those
cells.

My attempts so far which include sumproduct, match, sumif, etc don't seem to
work; sumproduct and sumif seem to want numeric values, match isn't working
because I have to use a wildcard character and the first match is not always
the right one, and I haven't found anything else that will evaluate multiple
cells and return a text value only when both cells meet my criteria.

Are there any other formulas I should play with? I'm reluctant to use array
formulas because this formula will include sumproduct and other formulas, and
my initial testing wasn't fruitful, and I was thinking it might have to do
with entering things like a sumproduct Within an array formula.

=Count(And(criteria1, criteria2))
does provide the expected result (1 match); I just can't figure out how to
tell which column it is in, and return the text from one of my target cells.

I welcome any advice-

Thank you,
Keith

returning text; alternative to sumproduct/sumif?
 

See if this is what you had in mind...

...........A..........B..........C
1........x...........z..........AAA
2........z...........z..........BBB
3........x...........y.........CCC
4........y...........x.........DDD

You want to lookup x in col A and y in col B and return the corresponding
value from col C.

Array entered** :

=INDEX(C1:C4,MATCH(1,(A1:A4="x")*(B1:B4="y"),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"ker_01" wrote in message
...
I need to check a range and identify each situation where two cells match
my
criteria, and in those situations, return the text value of one of those
cells.

My attempts so far which include sumproduct, match, sumif, etc don't seem
to
work; sumproduct and sumif seem to want numeric values, match isn't
working
because I have to use a wildcard character and the first match is not
always
the right one, and I haven't found anything else that will evaluate
multiple
cells and return a text value only when both cells meet my criteria.

Are there any other formulas I should play with? I'm reluctant to use
array
formulas because this formula will include sumproduct and other formulas,
and
my initial testing wasn't fruitful, and I was thinking it might have to do
with entering things like a sumproduct Within an array formula.

=Count(And(criteria1, criteria2))
does provide the expected result (1 match); I just can't figure out how to
tell which column it is in, and return the text from one of my target
cells.

I welcome any advice-

Thank you,
Keith

returning text; alternative to sumproduct/sumif?
 

Hi,

```````````-
jjhh77

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"ker_01" wrote in message
...
I need to check a range and identify each situation where two cells match
my
criteria, and in those situations, return the text value of one of those
cells.

My attempts so far which include sumproduct, match, sumif, etc don't seem
to
work; sumproduct and sumif seem to want numeric values, match isn't
working
because I have to use a wildcard character and the first match is not
always
the right one, and I haven't found anything else that will evaluate
multiple
cells and return a text value only when both cells meet my criteria.

Are there any other formulas I should play with? I'm reluctant to use
array
formulas because this formula will include sumproduct and other formulas,
and
my initial testing wasn't fruitful, and I was thinking it might have to do
with entering things like a sumproduct Within an array formula.

=Count(And(criteria1, criteria2))
does provide the expected result (1 match); I just can't figure out how to
tell which column it is in, and return the text from one of my target
cells.

I welcome any advice-

Thank you,
Keith

returning text; alternative to sumproduct/sumif?
 

Hi,

Try this

=INDEX(A5:C8,MATCH(1,index((A5:A8="x")*(B5:B8="y") ,,),0),3)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"ker_01" wrote in message
...
I need to check a range and identify each situation where two cells match
my
criteria, and in those situations, return the text value of one of those
cells.

My attempts so far which include sumproduct, match, sumif, etc don't seem
to
work; sumproduct and sumif seem to want numeric values, match isn't
working
because I have to use a wildcard character and the first match is not
always
the right one, and I haven't found anything else that will evaluate
multiple
cells and return a text value only when both cells meet my criteria.

Are there any other formulas I should play with? I'm reluctant to use
array
formulas because this formula will include sumproduct and other formulas,
and
my initial testing wasn't fruitful, and I was thinking it might have to do
with entering things like a sumproduct Within an array formula.

=Count(And(criteria1, criteria2))
does provide the expected result (1 match); I just can't figure out how to
tell which column it is in, and return the text from one of my target
cells.

I welcome any advice-

Thank you,
Keith