COUNTIF(B1:B5=the value "20" & A1:A5=the lette

[dw8ite]

I need help with a formula that will do what is stated in the Subject line. I know this isn't correct, but it explains the result I'm shooting for. How do I test both columns A & B and return the number of cells where the test is passed?

 

[ADAMC]

Cant see whole of the subject line....
Whats the formula you need?

 

[cdvdm]

Can you post the subject again? It is cut off after "...A1:A5=the lette"

Based on your subject, the first part should be =COUNTIF(B1:B5,"=20")

 

[PaddyD]

counting for 2 conditions:

=sumproduct(--(range1=contidtion1),--(range2=condition2))

so:

sumproduct(--(a1:a5="a"),--(b1:b5=20))

...or whatever

 

[dw8ite]

Thanks for the reply and sorry the subject line was truncated. The formula was COUNTIF(B1:B5=the value "20" & A1:A5=the letter "s").

 

[ADAMC]

You need:

=SUMPRODUCT(--(A1:A5="S"),--(B1:B5=20))

 

[dw8ite]

Thanks so much...SUMPRODUCT(--(A1:A5="S"),--(B1:B5=20)) did the trick. I thought SUMPRODUCT was some sorta multiplication formula.
Oh well.

Thanks again.

 

[ADAMC]

Your very welcome:

Taken from excel help:

SUMPRODUCT returns the sum of the products of corressponding ranges or arrays.