How to count cells that contain specific words in two columns

[d4d4ng]

Hi All,

I want to count cells that contain specific words in two columns, as:

[INDENT]    Column A Column B
[/INDENT]Row 1    apples   red
Row 2    apples   green
Row 3    banana   yellow
Row 4    apples   red

I want to know the formula to count how many cells in column A with apples AND column B with red.

I have tried :

=countif(and(A1:A4,"apples"),(B1:B4,"red"))

but no go

Thanks in advance

 

[jim may]

=SUMPRODUCT(--(A1:A4="apples"),--(B1:B4="red"))

 

[jbeaucaire]

=SUMPRODUCT(--($A$1:$A$4-"apples"), --($B$1:$B$4="red"))

Excel 2007+ gives you another function:

=COUNTIFS(A1:A4, "apples", B1:B4, "red")

 

[T. Valko]

Hi All,

I want to count cells that contain specific words in two columns, as:

[INDENT]   Column A Column B
[/INDENT]Row 1    apples   red
Row 2    apples   green
Row 3    banana   yellow
Row 4    apples   red

I want to know the formula to count how many cells in column A with apples AND column B with red.

I have tried :

=countif(and(A1:A4,"apples"),(B1:B4,"red"))

but no go

Thanks in advance

Try one of these...

Use cells to hold the criteria:

• D1 = apples
• E1 = red

This formula will work in Excel 2007 and later:

=COUNTIFS(A1:A4,D1,B1:B4,E1)

This formula will work in ALL versions of Excel:

=SUMPRODUCT(--(A1:A4=D1),--(B1:B4=E1))

 

[d4d4ng]

@jim may, jbeaucaire and T. Valko,

Highly appreciate your prompt responses

I will try to implement each of your way, and back to you later, it's nearly midnite in here.

Thank you all.

Best regards,
d4d4ng