COUNTIF with criteria from two columns

[xljruser]

Hi all, longtime lurker, first time poster

I've looked all over this site, but cant seem to find my answer, which is a first. I am trying to create a COUNTIF formula, with criteria from 2 columns, A and B

Column A is a list of FRUITS, and column B is the COLOUR. I would like to use some sort of COUNT formula which will tell me how many Apples are "red"
Apples can be red, green and yellow, but I would like to know how many apples are only red.

I used =IF(AND(A4:A27="apples",B4:B27="red"),"match","no") for each line just to test it. But i would like to use a COUNTIF statement. I thought maybe could use the same approach with =COUNTIF(AND.......)but just cant seem to get it

Any help would be great,
Thanks in andvance

 

[Von Pookie]

The COUNTIF function doesn't accept multiple criteria; you need to use the SUMPRODUCT function:

Book1
	A	B	C	D
1	Fruits	Color	#ofredapples:	3
2	Apples	Red
3	Apples	Green
4	Apples	Red
5	Apples	Red
6	Apples	Yellow
7	Apples	Green
8	Apples	Green
9	Apples	Yellow
10	Apples	Yellow
Sheet1

 

[xljruser]

Thanks for the quick response. Works like a charm.

Now, what about if I had a Column C, an it had the price of all the fruits, and I wanted to know the total price of all apples which are red. Again, I think it would be a version of the =SUM(IF(A1:A50="APPLES",C2:C25)) This wil give me the sum of the price of apples, but can i insert another critera in there,

SUM(IF(A1:A50="Apples", B1:B50="red",C1:C50))

I know this is incorrect, but it's where i want to start. I tired to use this with the =SUMPRODUCT(--($A$1:$A$10="Apples"),--($B$1:$B$10="Red")) formula, but stuck again.

 

[fairwinds]

Just add the column to sum at the end:

=SUMPRODUCT(--($A$1:$A$10="Apples"),--($B$1:$B$10="Red"),$C$1:$C$10)

 

[xljruser]

OMG

That's what i tried, but i forgot the coma.

thanks so much