Returning a value that meet multiple criteria

jdangle

New Member
Joined
Apr 15, 2005
Messages
7
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.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
search the board for multi key lookup - post back if you can't get it sorted.

edit: though given the desire to return multiple values, an advanced filter is probably what you need:
Book1
ABCDEFG
1NameStatusCode
2JohnOpenaTRUE
3JohnClosedb
4JohnPendingCodeNameStatusCode
5FrankOpendJohnOpena
6FrankClosedeJohnOpenf
7JohnOpenf
Sheet5


criteria range in yellow, criteria is:

=AND(A2="John",B2="Open")

with copy to another location option...
 

jdangle

New Member
Joined
Apr 15, 2005
Messages
7
Im having trouble getting this equation sorted. Does anybody else have an idea how to work this equation?
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
"Im having trouble getting this equation sorted"

A little more info about what this means might help.

"Does anybody else have an idea how to work this equation?"

Once you've provided the info, no doubt some one else will be more than willing to help out.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,130
Messages
5,835,573
Members
430,367
Latest member
User800

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top