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

#### dw8ite

##### New Member
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?

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

##### Well-known Member
Cant see whole of the subject line....
Whats the formula you need?

#### cdvdm

##### New Member
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")

##### MrExcel MVP
counting for 2 conditions:

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

so:

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

...or whatever

#### dw8ite

##### New Member
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").

##### Well-known Member
You need:

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

#### dw8ite

##### New Member
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.

##### Well-known Member

Taken from excel help:

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

Replies
4
Views
269
Replies
1
Views
133
Replies
6
Views
341
Replies
6
Views
249
Replies
2
Views
261

1,191,482
Messages
5,986,840
Members
440,052
Latest member
silverandcoldmc

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back