Counting occurances of pairs of values

M

Martin Dowie

I have 2 columns in my spreadsheet, I would like to be able to count
the number of times pairs of values occur, e.g.

col a | col b
------+------
foo | yes
bar | yes
foo | no
foo | yes
bar | yes
bar | yes

I'd like to search for all 'foo' AND 'yes' and then all 'bar' AND
'yes', so that I can generate a table:

search | count
-------+------
foo | 2
bar | 3

I've tried various combos of COUNTIF, SUM/IF but with no success.
Anyone any ideas?

Cheers

-- Martin
 
A

Andy B

Hi

Try using:
=SUMPRODUCT((A2:A50="foo")*(B2:B50="yes"))
and
=SUMPRODUCT((A2:A50="bar")*(B2:B50="yes"))

The "foo" and the "bar" value can be read from a cell, if necessary. The
ranges must be the same size and cannot be a full column.

Andy.
 
M

Martin Dowie

Andy B said:
Try using:
=SUMPRODUCT((A2:A50="foo")*(B2:B50="yes"))
and
=SUMPRODUCT((A2:A50="bar")*(B2:B50="yes"))

The "foo" and the "bar" value can be read from a cell, if necessary. The
ranges must be the same size and cannot be a full column.
Click to expand...

Andy

Major thanks - that did the job nicely!

Cheers,

-- Martin

Play Marbles @ www.marillion.com
 
Top