# COUNTIFS query

#### jacko2401

##### New Member
Hi

I have a worksheet that I am trying to count 2 criteria and thought I could use COUNTIFS. The worksheet is set up as such

A B C D
1 Blue Joe Dave Jeff
2 Green Jeff Dave Bob
3 Blue Steve Pete Dave

I want to find the occurances of when column A is a specific colour and columns B, C or D has a specific name in the same row (e.g. Blue and Dave should result in 2 - Blue in A1 with Dave in C1 and Blue in A3 with Dave in D3)

I've tried =COUNTIFS(A1:A3,"Blue",B1:D3,"Dave") and =COUNTIFS(A1:C3,"Blue",B1:D3,"Dave") but neither come up with the right answer of 2. The first attempt gives a #VALUE which I believe is because the ranges have to be equal and the 2nd attempt comes back with the wrong answer of 0.

Please can anyone confirm the correct function and application of it.

Many thanks

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).
Hi

I have a worksheet that I am trying to count 2 criteria and thought I could use COUNTIFS. The worksheet is set up as such

A B C D
1 Blue Joe Dave Jeff
2 Green Jeff Dave Bob
3 Blue Steve Pete Dave

I want to find the occurances of when column A is a specific colour and columns B, C or D has a specific name in the same row (e.g. Blue and Dave should result in 2 - Blue in A1 with Dave in C1 and Blue in A3 with Dave in D3)

I've tried =COUNTIFS(A1:A3,"Blue",B1:D3,"Dave") and =COUNTIFS(A1:C3,"Blue",B1:D3,"Dave") but neither come up with the right answer of 2. The first attempt gives a #VALUE which I believe is because the ranges have to be equal and the 2nd attempt comes back with the wrong answer of 0.

Please can anyone confirm the correct function and application of it.

Many thanks

I think the ranges should all be in the same column?

So =COUNTIFS(A1:A3,"Blue",B1:D3,"Dave") should instead be =COUNTIFS(A1:A3,"Blue",B1:B3,"Dave")

(B1 to B3 instead of B1 to D3)

Does that help?

Excel Workbook
ABCDEFGH
1
21BlueJoeDaveJeffblue2
32GreenJeffDaveBobDave
43BlueStevePeteDave
Sheet4

Excel Workbook
ABCDEFGH
1********
21BlueJoeDaveJeff*blue2
32GreenJeffDaveBob*Dave*
43BlueStevePeteDave***
Sheet4

I am trying to input the results into 1 element of a 2 dimensional array in VB. The other elements are performing other calculations. Is this possible to do or create a new array in vb with this calculation?

Replies
1
Views
197
Replies
5
Views
415
Replies
1
Views
201
Replies
7
Views
191
Replies
14
Views
1K

1,203,060
Messages
6,053,303
Members
444,650
Latest member
bookendinSA

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