PLS HLP! Countifs Syntax Problem?

[dee]

Hi,

Let's say I have the following in two columns, with a cell that contain the
criteria I wish to count:

red apple orange
pear green applies
peach cherries
red grapes green grapes

apple
grapes

I wish to use Countifs to count the number of apples, no matter the colour,
in both ranges, and grapes, no matter the colour, in both ranges.

I have tried and tried, but seem to always come up with 0.

PLS HLP!

 

[JMB]

where "apple" is in A6 and your data is in A1:B3, try:

=COUNTIF(A1:B3,"*"&A6)

 

[dee]

Hi and thanks for your response.

The problem is when I'm trying to count two separate items in two ranges
using the CountifS function is Excel 2007.

Would you know about that?
Thank you.

 

[JMB]

I don't have XL07. From your post, I did not get that you wanted to do a
multiple criteria count (which is what I understand countifs is intended). I
thought your request was to count the cells w/apples or grapes, which I think
would be

Countif(...., "*apple")+Countif(....., "*grape")

which returns 5 for me. If this isn't what you want, you should include
additional details about what you want to do, the formula you are currently
using, and what answer you are expecting for the sample data given and
hopefully someone w/XL07 will offer a suggestion.

 

[Teethless mama]

=SUM(COUNTIF(A1:B4,{"*apple*","*grapes*"}))

 

[Roger Govier]

Hi Dee

Counifs does not work in the way you are thinking.

=COUNTIFS(A1:A4,"*apples",B1:B4,"*grapes")
will only return a result when apples and grapes occur in the same row
In your example it will be 0

If Green Grapes and Oranges were transposed in column B, then you would
get a result of 1, as both Apples and Grapes appear on row 1
If you used the following formula on this amended data however, it will
still return 0, as the correct criteria are not being set
=COUNTIFS(A1:B4,"*apples",A1:B4,"*Grapes")

You have been given the correct method in either JMB's second post or
Teethless Mama's post. There is no different solution in XL2007 to
previous versions.