Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumif or Better Alternative? | Excel Worksheet Functions | |||
SUMPRODUCT not returning TEXT value | Excel Discussion (Misc queries) | |||
Returning an alternative value if lookup cannot be found in array | Excel Discussion (Misc queries) | |||
SUMIF Alternative? | Excel Worksheet Functions | |||
SUMIF Alternative? | Excel Discussion (Misc queries) |