Countifs with Named Lists

[Bossi]

I have a countifs formula that's basically this:

=countifs(range1,NamedList,range2,NamedList)

What I want to happen is that it'll count anytime anything in both ranges matches one of the items in the Named List.

What's actually happening is that it'll only count when both Range1 and Range2 simultaneously have the exact same list item.

I've included an image example to hopefully give a sense of what I'm trying to do. At the left I have my NamedList and each range. Then at the right I show the results I want on the left, and the results this formula is giving on the right.

I feel like I'm missing something obvious to get this to count the way I want it to, but I just can't figure it out... it feels like the code I'm using should work the way I want it to . I'm hoping to avoid brute forcing it.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Bossi]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Updated! (It's Office 365)

 

[Fluff]

Thanks for that
How about

Fluff.xlsm
	A	B	C	D	E
1
2	Alice		Alice	Dave	2
3	Bob		Alice	Bob
4	Charlie		Bob	Dave
5			Alice	Charlie
6			Dave	Charlie
Data

Cell Formulas
Range	Formula
E2	E2	=SUM(COUNTIFS(A2:A4,D2:D6)*COUNTIFS(A2:A4,C2:C6))

 

[Bossi]

That works for the question I posted! \o/

But now a follow-up as I try and adapt this to my actual spreadsheet...

So let's say I now have a third metric where I want to tally both whether someone in the NamedList was one of the people in that range, *and* an additional metric.

In this example I add an additional Winner column. Now I want to know both if Range1 and Range2 include any of the names in NamedList, and whether that pairing won as the Green Team. In this example the answer should be one.

Again, I'm guessing there's an easy tweak to your formulae but I've not yet managed to figure it out...

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F
1
2	Alice		Green	Alice	Dave	1
3	Bob		Green	Alice	Bob
4	Charlie		Blue	Bob	Dave
5			Blue	Alice	Charlie
6	Green		Blue	Dave	Charlie
Data

Cell Formulas
Range	Formula
F2	F2	=SUM(COUNTIFS(A2:A4,E2:E6)*COUNTIFS(A2:A4,D2:D6)*COUNTIFS(A6,C2:C6))

 

[Bossi]

That works! Thanks!!

I actually ended up trying that in my own attempt to solve it & it didn't work when I'd tried it... it turns out that if I write "Green" instead of reference to A6 then the formula doesn't work.

It's easy to work around that, but now I'm puzzling over why the formula seems to have issues with that distinction. It's just a curiosity at this point in case you happen to know the answer, but no worries if that's in the category of "Excel sometimes has weird quirks & that's just the way it is"

 

[Fluff]

The first argument of countifs must be a range.