COUNTIFS() with named ranges in each criterion

[dralkyr]

I imagine it's just how the array is handled. I need a count if the name in A is in the first named range and the name in B is in the second named range. My ranges are just called "first" and "second." I tried =COUNTIFS(A1,first,B1,second) but it returns 0. I tested just on each criterion and range, which is on the right side of my table below. It looks like "Jenny" is failing because, as I can surmise, the matching value in the named range is not in the same position as the value itself.

How can I use multiple named ranges as the criteria for COUNTIFS() and other similar formulas?

[TABLE="width: 500"]
<tbody>[TR]
[TD]Spencer[/TD]
[TD]Jenny[/TD]
[TD][/TD]
[TD]Spencer[/TD]
[TD]Jake[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Ross[/TD]
[TD]Evan[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Josh[/TD]
[TD]Jenny[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

The syntax is:

=COUNTIFS(first,A1,second,B1)

 

[dralkyr]

That still comes up as 0 for me.

 

[Aladin Akyurek]

That still comes up as 0 for me.

spencer and jenny do not occur together in the data, hence 0.

 

[dralkyr]

Ok, so how can I overcome this? I have two lists of employees and need to check that someone from the first list is in one column and someone from the other is in the other column.

 

[Aladin Akyurek]

Ok, so how can I overcome this? I have two lists of employees and need to check that someone from the first list is in one column and someone from the other is in the other column.

What should be the result for spencer and jenny for the sample you posted?

 

[dralkyr]

What should be the result for spencer and jenny for the sample you posted?

The final output should be 1. There is 1 row where column A has an entry in 'first' and B has an entry in 'second.' It would be like using =SUMPRODUCT(--(A1=first)) but that doesn't work as a countifs criterion.

 

[Aladin Akyurek]

The final output should be 1. There is 1 row where column A has an entry in 'first' and B has an entry in 'second.' It would be like using =SUMPRODUCT(--(A1=first)) but that doesn't work as a countifs criterion.

I think your definition is not clear. Try to describe in words what is desired (instead of forwarding possibly misleading formula bits).

Suppose spencer is in first and jenny is not in second: what is the result?
Suppose spencer is not in first and jenny is in second: what is the result?
Suppose spencer is not in first and jenny is not insecond: what is the result?

 

[dralkyr]

I think your definition is not clear. Try to describe in words what is desired (instead of forwarding possibly misleading formula bits).

Sorry, I don't mean to be difficult. I have a list of names in one column and a list of other names in another column. I have two named ranges, one giving the valid names for each column. I want the count of all rows where both the first column is a name from the first named range and the second column is a name from the second named range.