How to subtract a named range from a formula

[Burrgogi]

In a worksheet, I have the following formula:
=COUNTA(A2:A4356)

It tallies up all the items listed in column A. They all contain mostly text values and not numerical values.

There are 2 items that I do not want to be counted in the overall total so I suppose the most obvious way would be to modify the formula to this:
=COUNTA(A2:A4356)-2

Is there be a better way to do so by creating a named range for each of those items? such as "Disregard_1" & "Disregard_2" and then using those named ranges in a formula like the one I'm using above?

 

[shift-del]

Hi

Your stratagy is ok if the two values are always present in column A.
Otherwise something like this:

=COUNTA(A2:A4356)-SUM(COUNTIFS(A2:A4356;"{"value1"."value2"}

 

[Burrgogi]

Hi

Your stratagy is ok if the two values are always present in column A.

Yes, they will always be located in Col. A. After I posted, i was able to figure things out. Here is my formula for now:

=COUNTA(A2:A4356)-COUNTA("Disregard_1")-COUNTA("Disregard_2")

Otherwise something like this:

=COUNTA(A2:A4356)-SUM(COUNTIFS(A2:A4356;"{"value1"."value2"}

I think you missed a closing parenthesis at the very end - no? At any rate, I will keep that one in mind for future reference in case I need it. Thanks.

 

[shift-del]

I think you missed a closing parenthesis at the very end - no?

You are right.

 

[Burrgogi]

You are right.

Just for sh*ts and giggles, I decided to give your formula a try. I've never seen that before so I wanted to see if it would work for my situation. I tried this:

=COUNTA(A2:A482)-SUM(COUNTIFS(A2:A482;"{"white"."purple"})

It does not work.

 

[shift-del]

Why?

Mappe1
	A	B	C	D	E
1			10	2	8
2	xxx			2
3	xxx
4	white
5	xxx			white
6	xxx			purple
7	purple
8	xxx
9	xxx
10	xxx
11	xxx
Tabelle1

Cell Formulas
Range	Formula
C1	C1	=COUNTA(A2:A11)
E1	E1	=C1-D1
D1	D1	=SUM(COUNTIFS(A2:A11,{"white";"purple"}))
D2	D2	=SUM(COUNTIFS(A2:A11,D5:D6))

 

[Burrgogi]

Why?

Upon reading your latest reply above, I see that you made some typos in the original solution you gave me.

=COUNTA(A2:A4356)-SUM(COUNTIFS(A2:A4356;"{"value1"."value2"}

1) There should be a comma after COUNTIFS(A2:A4356 You have a semicolon.
2) Between "value1" & "value2", there should be a comma, not a period.
3) You missed 2 closing parenthesis at the very end of the formula.

 

[shift-del]

In my first posting I translated the formula manually. So I missed to change the semicolon into a comma.
But a experienced user should have noticed that.

 

[Burrgogi]

In my first posting I translated the formula manually. So I missed to change the semicolon into a comma.

You missed much more than just a simple comma. Please see my revised answer above.

But a experienced user should have noticed that.

That is a ridiculous answer. You and I have never interacted on the boards before. How would you know what kind of skill level I have with that formula?
Also if you're going to answer someone's thread, shouldn't you be the one providing an accurate formula that works or at least test it first?