V-lookup and Countif
 

I'm trying to create a formula that will look up criteria in one column and
count the result of another column. Here is some data as an example:

Fruit Have in Stock
Orange X
Pear X
Apple
Orange X
Orange
Apple X

I'm trying to create a formula that looks up "Orange" in column A and then
counts the number of "X" in column b to return a results. So my result for
Oranges should be 2 because out of the 3 listed only 2 have an "X" marked
next to it.

V-lookup and Countif
 

Hi,

Try this

=SUMPRODUCT((A1:A10="Orange")*(B1:B10="X"))

In practice use cell references for the criteria

=SUMPRODUCT((A1:A10=C1B1:B10=D1))

Mike

"thm" wrote:

I'm trying to create a formula that will look up criteria in one column and
count the result of another column. Here is some data as an example:

Fruit Have in Stock
Orange X
Pear X
Apple
Orange X
Orange
Apple X

I'm trying to create a formula that looks up "Orange" in column A and then
counts the number of "X" in column b to return a results. So my result for
Oranges should be 2 because out of the 3 listed only 2 have an "X" marked
next to it.

V-lookup and Countif
 

I meant

=SUMPRODUCT((A1:A10=C1)*(B1:B10=D1))

"Mike H" wrote:

Hi,

Try this

=SUMPRODUCT((A1:A10="Orange")*(B1:B10="X"))

In practice use cell references for the criteria

=SUMPRODUCT((A1:A10=C1B1:B10=D1))

Mike

"thm" wrote:

I'm trying to create a formula that will look up criteria in one column and
count the result of another column. Here is some data as an example:

Fruit Have in Stock
Orange X
Pear X
Apple
Orange X
Orange
Apple X

I'm trying to create a formula that looks up "Orange" in column A and then
counts the number of "X" in column b to return a results. So my result for
Oranges should be 2 because out of the 3 listed only 2 have an "X" marked
next to it.