I want to count the number of times a row has two specific words, in two
different cells eg.
If column A contains the word 'Widget' and column B contains the word 'Sale'
I want to count the number of times this occurs.

I'm sure this is easy but I've spent hours trying to get it to work.

Check out Chip Pearson's Array Formula explanation...
http://www.cpearson.com/excel/array.htm

An array formula is created using Ctrl+Shift+Enter to surround the formula
with braces { }. You can't just type them :O.

Your formula would look something like...
{=SUM(IF($A1:$A1000="widget",1,0)*IF($B1:$B1000="s ale",1,0))}

Make sure you read Chip's site (above) to understand 'WHY' this works.
Otherwise I've given you a meal instead of helped you learn how to fish for
yourself.

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Jazzman10" wrote:

I want to count the number of times a row has two specific words, in two
different cells eg.
If column A contains the word 'Widget' and column B contains the word 'Sale'
I want to count the number of times this occurs.

I'm sure this is easy but I've spent hours trying to get it to work.

Thanks for that - it works a treat and I read through Chip Pearson's array
formula explanation. However here's another question you might be able to
solve:

The formula works fine if put into a cell that hasn't been used before. But
if I try to enter (ctrl+shift+enter) it into a cell that has previously had a
deleted formula in it just puts the formula in without making it an array
formula (no {} either side) and won't work?

"Gary L Brown" wrote:

Check out Chip Pearson's Array Formula explanation...
http://www.cpearson.com/excel/array.htm

An array formula is created using Ctrl+Shift+Enter to surround the formula
with braces { }. You can't just type them :O.

Your formula would look something like...
{=SUM(IF($A1:$A1000="widget",1,0)*IF($B1:$B1000="s ale",1,0))}

Make sure you read Chip's site (above) to understand 'WHY' this works.
Otherwise I've given you a meal instead of helped you learn how to fish for
yourself.

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Jazzman10" wrote:

I want to count the number of times a row has two specific words, in two
different cells eg.
If column A contains the word 'Widget' and column B contains the word 'Sale'
I want to count the number of times this occurs.

I'm sure this is easy but I've spent hours trying to get it to work.

You could try a normal sumproduct and avoid the hassle of having to
worry about CTRL Shift and Enter

=SUMPRODUCT((A1:A1000="widget")*(B1:B1000="sale"))

Don't know about your missing {} in the array version. Wha happens if
you copy and paste the array formula?

Rgds


On Mon, 26 Jun 2006 07:59:01 -0700, Jazzman10
wrote:

Thanks for that - it works a treat and I read through Chip Pearson's array
formula explanation. However here's another question you might be able to
solve:

The formula works fine if put into a cell that hasn't been used before. But
if I try to enter (ctrl+shift+enter) it into a cell that has previously had a
deleted formula in it just puts the formula in without making it an array
formula (no {} either side) and won't work?

"Gary L Brown" wrote:

Check out Chip Pearson's Array Formula explanation...
http://www.cpearson.com/excel/array.htm

An array formula is created using Ctrl+Shift+Enter to surround the formula
with braces { }. You can't just type them :O.

Your formula would look something like...
{=SUM(IF($A1:$A1000="widget",1,0)*IF($B1:$B1000="s ale",1,0))}

Make sure you read Chip's site (above) to understand 'WHY' this works.
Otherwise I've given you a meal instead of helped you learn how to fish for
yourself.

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Jazzman10" wrote:

I want to count the number of times a row has two specific words, in two
different cells eg.
If column A contains the word 'Widget' and column B contains the word 'Sale'
I want to count the number of times this occurs.

I'm sure this is easy but I've spent hours trying to get it to work.

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

I can't duplicate your issue. It works first time, every time for me.

--
Gary Brown


"Jazzman10" wrote:

Thanks for that - it works a treat and I read through Chip Pearson's array
formula explanation. However here's another question you might be able to
solve:

The formula works fine if put into a cell that hasn't been used before. But
if I try to enter (ctrl+shift+enter) it into a cell that has previously had a
deleted formula in it just puts the formula in without making it an array
formula (no {} either side) and won't work?

"Gary L Brown" wrote:

Check out Chip Pearson's Array Formula explanation...
http://www.cpearson.com/excel/array.htm

An array formula is created using Ctrl+Shift+Enter to surround the formula
with braces { }. You can't just type them :O.

Your formula would look something like...
{=SUM(IF($A1:$A1000="widget",1,0)*IF($B1:$B1000="s ale",1,0))}

Make sure you read Chip's site (above) to understand 'WHY' this works.
Otherwise I've given you a meal instead of helped you learn how to fish for
yourself.

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Jazzman10" wrote:

I want to count the number of times a row has two specific words, in two
different cells eg.
If column A contains the word 'Widget' and column B contains the word 'Sale'
I want to count the number of times this occurs.

I'm sure this is easy but I've spent hours trying to get it to work.