Count Formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
ceej said:
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
 
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??
 
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
 
=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)
 
=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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top