Help counting items please

[Dave \Doc\ Corio]

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!

 

[Don Guillett]

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

 

[Dave \Doc\ Corio]

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!!

 

[Bob Phillips]

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

--

HTH

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

 

[RagDyeR]

Try this:

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

--

HTH,

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

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!!

 

[Dave \Doc\ Corio]

This seems to have worked!

Many thanks to all!