Prev: Question about a Formula
Next: Sorting a protected set of cells
From: thm on 23 Dec 2009 14:39
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.
From: Mike H on 23 Dec 2009 14:59
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.
From: Mike H on 23 Dec 2009 15:43
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.
 | 
Pages: 1
Prev: Question about a Formula
Next: Sorting a protected set of cells