Help counting items please

Having a bit of trouble counting items on the following example:

A B
1 Apples Pears Bananas Green
2 Peaches Apples Grapes Yellow
3 Pears Oranges Tomato Green
4 Corn Squash Apples Green

I'd like to use a formula to count how many cells in column A contain
"Apples", while at the same time column B contains "Green"

I can do a conditional sum to find out how many cells in column A
contain "Apples Pears Bananas" and column B "Green", but can anyone tell
me how to look for just the one word within a cell, when the word
position could be anywhere within the cell, and conditionally sum it
with column B?

Many thanks in advance!

try
=sumproduct((a2:a200="Apples")*(d2:d200="Green"))

--
Don Guillett
SalesAid Software
[email protected]
"Dave "Doc" Corio" <[email protected]> wrote in message
news:[email protected]...
> Having a bit of trouble counting items on the following example:
>
> A B
> 1 Apples Pears Bananas Green
> 2 Peaches Apples Grapes Yellow
> 3 Pears Oranges Tomato Green
> 4 Corn Squash Apples Green
>
> I'd like to use a formula to count how many cells in column A contain
> "Apples", while at the same time column B contains "Green"
>
> I can do a conditional sum to find out how many cells in column A
> contain "Apples Pears Bananas" and column B "Green", but can anyone tell
> me how to look for just the one word within a cell, when the word
> position could be anywhere within the cell, and conditionally sum it
> with column B?
>
> Many thanks in advance!
>

I've tried this as both a regular formula and a conditional sum, and it
doesn't seem to work. Cells in column A contain more than one word, so
wouldn't this formula use only cells that contain only the word "Apples"?

I sure appreciate the help!!


Don Guillett wrote:
> try
> =sumproduct((a2:a200="Apples")*(d2:d200="Green"))
>

=SUMPRODUCT(--(ISNUMBER(SEARCH("Apples",A2:A200))),--(B2:B200="Green"))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dave "Doc" Corio" <[email protected]> wrote in message
news:[email protected]...
> I've tried this as both a regular formula and a conditional sum, and it
> doesn't seem to work. Cells in column A contain more than one word, so
> wouldn't this formula use only cells that contain only the word "Apples"?
>
> I sure appreciate the help!!
>
>
> Don Guillett wrote:
> > try
> > =sumproduct((a2:a200="Apples")*(d2:d200="Green"))
> >

Try this:

=SUMPRODUCT((ISNUMBER(SEARCH("Apples",A1:A4))*(B1:B4="Green")))

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

"Dave "Doc" Corio" <[email protected]> wrote in message
news:[email protected]...
I've tried this as both a regular formula and a conditional sum, and it
doesn't seem to work. Cells in column A contain more than one word, so
wouldn't this formula use only cells that contain only the word "Apples"?

I sure appreciate the help!!


Don Guillett wrote:
> try
> =sumproduct((a2:a200="Apples")*(d2:d200="Green"))
>

This seems to have worked!

Many thanks to all!


RagDyeR wrote:
> Try this:
>
> =SUMPRODUCT((ISNUMBER(SEARCH("Apples",A1:A4))*(B1:B4="Green")))
>