Count Formula

[ceej]

Good Afternoon ...
This questions is probably redundant, however, my question is how to write
the formula to count, and excluding certain criteria. For example:
Column A
1111
2921
2222
2921
3344
3333
4444
Count the entries, but do not include the numbers 2921 and 3344. Results
should equal 4.

 

[VBA Noob]

=sumproduct(--(a1:a7<>2921)*(--(a1:a7<>3344))

 

[Maistrye]

Good Afternoon ...
This questions is probably redundant, however, my question is how t
write
the formula to count, and excluding certain criteria. For example:
Column A
1111
2921
2222
2921
3344
3333
4444
Count the entries, but do not include the numbers 2921 and 3344.
Results
should equal 4.

One possibility is:

=COUNT(A1:A7)-COUNTIF(A1:A7,"2921")-COUNTIF(A1:A7,"3344")

Scot

 

[ceej]

FYI ... my data has 89 rows x 9 columns, with the criteria being in column G.
I tried the formula and the results were the exact number of rows, 89. I
double-checked the data, and it is numberic. Any other suggestions??

 

[VBA Noob]

So your criteria is in the middle of your table which is odd??

If you put your criteria in J1 and J2 then this should work

=SUMPRODUCT(--(A1:I89<>J1)*(--(A1:I89<>J2)))

VBA Noob

 

[Bob Phillips]

=SUMPRODUCT(--(A1:I89<>J1),--(A1:I89<>J2))

or

=SUMPRODUCT((A1:I89<>J1)*(A1:I89<>J2))

no need for both

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bob Phillips]

=COUNT(IF(A1:I89<>{2921,23344},A1:I89))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)