Count Formula-probably redundant

[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.

--
Thank you ... ceej

[VBA Noob]

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

[Maistrye]

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

[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??
--
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
>
>

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

"VBA Noob" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

"ceej" <[email protected]> wrote in message
news:[email protected]...
> 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
> >
> >