Using a Named Range List as a criteria in COUNTIFS

[Steven975]

I'm looking for some assistance using COUNTIFS with a named range (list) as a criteria.

It seems to me like it should work like this, but it doesn't. After some research I found a place that said this would work if the named range was a single cell, but I need it to reference a list, as I need the ability to add new criteria, without needing to change this formula. Is there a way to do this?
=COUNTIFS(A:A,"Jan",B:B,"Call",C:C,"<>OM",D:D"<>"&List)

In the example below it should return "2"

A	B	C	D
Jan	Call	OM	Left Message
Jan	Call	Call List	Customer Contacted First
Jan	Call	Follow Up	Contacted
Jan	Call	OM	No Contact
Jan	Call	Call List	No Contact
Jan	Call	Call List	Contacted

List

Customer Contacted First

No Contact

 

[Toadstool]

Can you add a worker column?

Book1
	A	B	C	D	E	F	G
1	Jan	Call	OM	Left Message	In List?		Count
2	Jan	Call	Call List	Customer Contacted First	False		2
3	Jan	Call	Follow Up	Contacted	True
4	Jan	Call	OM	No Contact	False
5	Jan	Call	Call List	No Contact	False
6	Jan	Call	Call List	Contacted	True
7
8	List
9	Customer Contacted First
10	No Contact
11
Sheet1

Cell Formulas
Range	Formula
G2	G2	=COUNTIFS(A:A,"Jan",B:B,"Call",C:C,"<>OM",E:E,TRUE)
E2:E6	E2	=ISNA(MATCH(D2,List,0))

 

[DanteAmor]

How about:

Book1
	A	B	C	D	E	F	G	H
1	Jan	Call	OM	Left Message	2			Customer Contacted First
2	Jan	Call	Call List	Customer Contacted First				No Contact
3	Jan	Call	Follow Up	Contacted
4	Jan	Call	OM	No Contact
5	Jan	Call	Call List	No Contact
6	Jan	Call	Call List	Contacted
sheet

Cell Formulas
Range	Formula
E1	E1	=SUMPRODUCT((A1:A6="Jan")*(B1:B6="Call")*(C1:C6<>"OM")*(--ISNA(MATCH(D1:D6,List))))

 

[J.Ty.]

Hi,

You need the following forula, which should be confirmed with Ctrl-Shift-Enter:
=SUM(COUNTIFS(A:A,"Jan",B:B,"Call",C:C,"<>OM",D:D,"<>"&A10:A11)-COUNTIFS(A:A,"Jan",B:B;"Call",C:C,"<>OM")

 

[Steven975]

How about:

Book1
	A	B	C	D	E	F	G	H
1	Jan	Call	OM	Left Message	2			Customer Contacted First
2	Jan	Call	Call List	Customer Contacted First				No Contact
3	Jan	Call	Follow Up	Contacted
4	Jan	Call	OM	No Contact
5	Jan	Call	Call List	No Contact
6	Jan	Call	Call List	Contacted
sheet

Cell Formulas
Range	Formula
E1	E1	=SUMPRODUCT((A1:A6="Jan")*(B1:B6="Call")*(C1:C6<>"OM")*(--ISNA(MATCH(D1:D6,List))))

It seems to be partially working. It doesn't seem to be referencing the list properly? There was an entry I put on the list earlier to test it, but I have removed it since. It is still removing those results, even though it is not on the list anymore. I added a new phrase to test and it isn't removing it.

 

[DanteAmor]

It seems to be partially working. It doesn't seem to be referencing the list properly? There was an entry I put on the list earlier to test it, but I have removed it since. It is still removing those results, even though it is not on the list anymore. I added a new phrase to test and it isn't removing it.

Sorry, I forgot a 0

=SUMPRODUCT((A1:A6="Jan")*(B1:B6="Call")*(C1:C6<>"OM")*(--ISNA(MATCH(D1:D6,List,0))))

 

[Steven975]

Sorry, I forgot a 0

=SUMPRODUCT((A1:A6="Jan")*(B1:B6="Call")*(C1:C6<>"OM")*(--ISNA(MATCH(D1:D6,List,0))))

Worked like a charm. Thanks!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.