# count Values in column B only if value in column A meets criteria (on the same row)

#### ener

##### New Member
 5 a 7 b 6 a 3 b 2 a 4 a 3 c 3 a

<tbody>
</tbody>

Hi guys, hopefully that's quite simple for some of you to answer. I have 2 columns which are NOT next to each other just shown here this way for simplification.

Now I want to look at Column A (the one with the numbers) and every time there is the number 3 it should look at Column B in the same row and count if there is either an 'a' or 'c' in there. So in this example the total count would be 2. First 3 has corresponding 'b' so no count next 3 has corresponding 'c' so count is 1 and last 3 has corresponding 'a' so total count is 2.

I am hoping to accomplish this without VBA and maybe a formula like DCOUNTA or something simpler.

Hope someone can help.

Thanks

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

#### par60056

##### Well-known Member
I know that there has to be a better way than this. But a way that works is better than none.

=COUNTIFS(A1:A8,3,B1:B8,"a")+COUNTIFS(A1:A8,3,B1:B8,"c")

#### Special-K99

##### Well-known Member
Try

=SUMPRODUCT((A1:A8=3)*((B1:B8="A")+(B1:B8="C")))

#### ener

##### New Member
Thank you very much guys. Both versions work perfectly.

Replies
3
Views
74
Replies
6
Views
262
Replies
2
Views
166
Replies
1
Views
86
Replies
4
Views
101

1,195,591
Messages
6,010,614
Members
441,558
Latest member
lambierules

### 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