Count Formula
 

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.

--
Thank you ... ceej

Count Formula
 

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


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=562254

Count Formula
 

ceej Wrote:
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.

--
Thank you ... ceej

One possibility is:

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

Scott


--
Maistrye
------------------------------------------------------------------------
Maistrye's Profile: http://www.excelforum.com/member.php...o&userid=36078
View this thread: http://www.excelforum.com/showthread...hreadid=562254

Count Formula
 

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??
--
Thank you ... ceej


"VBA Noob" wrote:


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


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=562254

Count Formula
 

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


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=562254

Count Formula
 

=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)

"VBA Noob" wrote in
message ...

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


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile:
http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=562254

Count Formula
 

=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)

"ceej" wrote in message
...
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??
--
Thank you ... ceej


"VBA Noob" wrote:


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


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile:
http://www.excelforum.com/member.php...o&userid=33833
View this thread:
http://www.excelforum.com/showthread...hreadid=562254