Is there an excel function that returns a range of values based on a condition?

To be开发者_StackOverflow社区tter illustrate what I am looking for I will give an example. Imagine a spreadsheet with two columns, Priority and Value.

P   V
H   x
H   x
M   x
M   y

I want to count the number of 'x' in Value if Priority is 'H'.

Countif uses COUNTIF(Range, Criteria) so I'm thinking I need a way to alter the range based on P and be able to use that range of rows in the Value column.

Any ideas? Thanks.

In Excel 2007 and later, you can use the new COUNTIFS worksheet function, as in something like:

=COUNTIFS(A2:A5,"H",B2:B5,"x")

How about the following (assuming data in A1:B5)

=SUM((A1:A5="H")*(B1:B5 = "x")) //returns 2

Note that this must be entered as an array formula (i.e. Press CTRL + SHIFT + ENTER)

You could use the builtin data filtering in Excel

Set up a third column with a formula that combines the first two columns, for instance,

=A2&"^"&B2

and then "count" it (according to the conditions you specified) with a cell containing

=COUNTIF(C2:C5,"H^x")