Excel - Counting text in multiple columns

[GarysBeau]

Hi apologies if this has been covered. Probably a simply query but I cant work it out.

Anyway

I have a range of text and I want to count them based on criteria from differing columns.

For example:

A--------B-------C
Blue----Apple-----
Blue----Apple-----
Yellow--Apple-----
Blue-----Pear------



So I want to count the amount of 'blue apples' (Dont know where you'd get blue apples mind) so it should return: 2

 

[Trevor G]

If using 2007 then CountIFs formula

Excel Workbook
	A	B	C	D	E
1	Colour	Fruit			countifs
2	Blue	Apple		Blue Apples	2
3	Blue	Apple		Yellow Apples	1
4	Yellow	Apple		Blue Pear	1
5	Blue	Pear
Sheet1

 

[AlphaFrog]

If you have Excel 2003 or earlier
=SUMPRODUCT(--(A1:A10="Blue"),--(B1:B10="Apple"))

If you have Excel 2007 or later...
=COUNTIFS(A:A, "Blue", B:B, "Apple")

 

[doofusboy]

One way to do this would be to put this formula in column C:

=A1&B1

Copy down as far as needed.

Then in cell D1 you could enter this formula to count for the first 5 rows:

=COUNTIF(C1:C5,A1&B1)

 

[GarysBeau]

Thanks everyone for your responses. I did forget to mention that its 2003 excel (Thats how our company rolls).

This leads me to my next question: How do I apply the same formula if there are several variations of 'blue', but I want to count them as 1 criteria

For example:

A-------------B-------C
Blue 2-------Apple-----
Blue 1-------Apple-----
Yellow-------Apple-----
Blue 999-----Pear------

That still returns '2'

 

[GarysBeau]

I'm wondering if I need to make a slight amendment to the formula as it still doesn't work.

 

[Trevor G]

Try this then

Excel Workbook
	A	B	C	D
1
2	Blue 2	Apple		2
3	Blue 1	Apple
4	Yellow	Apple
5	Blue 999	Pear
Sheet2

 

[GarysBeau]

Great, thanks!