COUNTIFS with Named Range in the Criteria

[Bokuchan]

My first directive was to count all the number of occurrences of jobs.
I started with a named range "Job" (A1:A10) containing numeric values associated to projects.
=COUNTIF(Job,">0") gave me the correct result based on the number of entries.

My second directive was to count all jobs as long as they were not "Added" or "Adjusted"
Made named range "DontCount" with the entries "Added" or "Adjusted".
Made named range "Description" (B1:B10) containing Data Validations using the range DontCount for the list.
modified the formula to =COUNTIFS(Job,">0",Description,"<>Added",Description,"<>Adjusted") which gives me the correct results.

Now i need to added a third Description to not be added. I have 24 sets of the Job and Description ranges and forumlas, names are adjusted for each set of course. So to save my self some time I would like to be able to adjust the formula to something like =COUNTIFS(Job,">0",Description,"<>DontCount") which doesn't work. But the idea is that I only have to adjust the named range "DontCount" and add the additional item. I would not have to adjust all 24 COUNTIFS

Thanx in advance.

 

[DRSteele]

Welcome to the forum.

Please provide some sample data together with expected results so that we can conduct experiments.

 

[Bokuchan]

Job Description

2
6 Adjusted
4
6
8 Added


DontCount

Added
Adjusted

=COUNTIFS(Job,">0",Description,"<>Added",Description,"<>Adjusted")
Result 3. Expected

=COUNTIFS(Job,">0",Description,"<>DontCount")
Result 5. UnExpected

 

[DRSteele]

If I understand correctly, this should provide the correct result.

Book1
	A	B	C	D
1	Job	Description		Don'tCount
2	2			Added
3	6	Adjusted		Adjusted
4	4
5	6
6	8	Added
7
8
9		Jobs >0 also not on Don'tCount list	3
Sheet42

Cell Formulas
Range	Formula
C9	{=COUNTIFS(Job,">0")-SUM(--(Description=TRANSPOSE(DontCount)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
Description	=Sheet42!$B$2:$B$6
DontCount	=Sheet42!$D$2:$D$3
Job	=Sheet42!$A$2:$A$6

 

[Jonmo1]

=COUNTIFS(Job,">0",Description,"<>DontCount")
Result 5. UnExpected

What result DID you expect? Why?