COUNTIFS query

[jacko2401]

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

 

[RaviWildcat]

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?

 

[Robert Mika]

Excel Workbook
	A	B	C	D	E	F	G	H
1
2	1	Blue	Joe	Dave	Jeff		blue	2
3	2	Green	Jeff	Dave	Bob		Dave
4	3	Blue	Steve	Pete	Dave
Sheet4

 

[jacko2401]

Excel Workbook
	A	B	C	D	E	F	G	H
1	*	*	*	*	*	*	*	*
2	1	Blue	Joe	Dave	Jeff	*	blue	2
3	2	Green	Jeff	Dave	Bob	*	Dave	*
4	3	Blue	Steve	Pete	Dave	*	*	*
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?