If two cells have specific text to count as 1

J

Jazzman10

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.
 
G

Gary L Brown

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="sale",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,
 
J

Jazzman10

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?
 
R

Richard Buttrey

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


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?
Click to expand...

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
Top