I have a two part question. I've been using a SUMPRODUCT equation to count the number of issues that meet multiple criterias.
ex) =SUMPRODUCT(--(Status = "Open"),--(Name = "John"))
Is there an equation that will do basically the same thing (look through a database to find if something meets multiple criteria) but will return a cell value in that same row rather than counting it? Basically, if status is "open" (C2) and name is "John" (B2), it will return the value in cell called Item (A2)?
ex)
A B C
Item Name Status
145A John Open
146A John Closed
147A Ryan Open
(so equation should only return 145A)
Part II is based on the result of the first question. Since SUMPRODUCT continues through the entire database to count all items meeting criteria, is there a way to return all the values (that meet the criteria) in a single cell? So instead of the equation giving me one value (the sumproduct) it will have every "Item" that is "open" and done by "John" in that cell.
ex) =SUMPRODUCT(--(Status = "Open"),--(Name = "John"))
Is there an equation that will do basically the same thing (look through a database to find if something meets multiple criteria) but will return a cell value in that same row rather than counting it? Basically, if status is "open" (C2) and name is "John" (B2), it will return the value in cell called Item (A2)?
ex)
A B C
Item Name Status
145A John Open
146A John Closed
147A Ryan Open
(so equation should only return 145A)
Part II is based on the result of the first question. Since SUMPRODUCT continues through the entire database to count all items meeting criteria, is there a way to return all the values (that meet the criteria) in a single cell? So instead of the equation giving me one value (the sumproduct) it will have every "Item" that is "open" and done by "John" in that cell.