FORMULAS
 

What formula counts how many cells contain two different criterias i.e. if a
cell contains Fr as well as B I want it to count but not if it contains one
or the other.

FORMULAS
 

=SUM(IF(NOT(ISERROR(FIND("Fr",A1:A10))),IF(NOT(ISE RROR(FIND("B",A1:A10))),1)))
then press Ctrl + Shift + Enter

Charles Chickering

HonorsOA wrote:
What formula counts how many cells contain two different criterias i.e. if a
cell contains Fr as well as B I want it to count but not if it contains one
or the other.

FORMULAS
 

Bob Phillips, What a I missing to make this "SumProduct" formula work?
=SUMPRODUCT((NOT(ISERROR(FIND("Fr",A1:A10))))*(NOT (ISERROR(FIND("B",A1:A10)))),(A1:A10))

Charles

Die_Another_Day wrote:
=SUM(IF(NOT(ISERROR(FIND("Fr",A1:A10))),IF(NOT(ISE RROR(FIND("B",A1:A10))),1)))
then press Ctrl + Shift + Enter

Charles Chickering

HonorsOA wrote:
What formula counts how many cells contain two different criterias i.e. if a
cell contains Fr as well as B I want it to count but not if it contains one
or the other.

FORMULAS
 

Nevermind, I was just making it too hard:
=SUMPRODUCT((NOT(ISERROR(FIND("Fr",A1:A10))))*(NOT (ISERROR(FIND("B",A1:A10)*))))

Charles

Die_Another_Day wrote:
Bob Phillips, What a I missing to make this "SumProduct" formula work?
=SUMPRODUCT((NOT(ISERROR(FIND("Fr",A1:A10))))*(NOT (ISERROR(FIND("B",A1:A10)))),(A1:A10))

Charles

Die_Another_Day wrote:
=SUM(IF(NOT(ISERROR(FIND("Fr",A1:A10))),IF(NOT(ISE RROR(FIND("B",A1:A10))),1)))
then press Ctrl + Shift + Enter

Charles Chickering

HonorsOA wrote:
What formula counts how many cells contain two different criterias i.e. if a
cell contains Fr as well as B I want it to count but not if it contains one
or the other.

FORMULAS
 

DAD,

as an aside, rather than NOT(ISERROR, I use ISNUMBER, makes it a tad more
readable

=SUMPRODUCT((ISNUMBER(FIND("Fr",A1:A10)))*(ISNUMBE R(FIND("B",A1:A10))))

--
HTH

Bob Phillips

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

"Die_Another_Day" wrote in message
ups.com...
Nevermind, I was just making it too hard:
=SUMPRODUCT((NOT(ISERROR(FIND("Fr",A1:A10))))*(NOT (ISERROR(FIND("B",A1:A10)*
))))

Charles

Die_Another_Day wrote:
Bob Phillips, What a I missing to make this "SumProduct" formula work?

=SUMPRODUCT((NOT(ISERROR(FIND("Fr",A1:A10))))*(NOT (ISERROR(FIND("B",A1:A10))
)),(A1:A10))

Charles

Die_Another_Day wrote:

=SUM(IF(NOT(ISERROR(FIND("Fr",A1:A10))),IF(NOT(ISE RROR(FIND("B",A1:A10))),1)
))
then press Ctrl + Shift + Enter

Charles Chickering

HonorsOA wrote:
What formula counts how many cells contain two different criterias
i.e. if a
cell contains Fr as well as B I want it to count but not if it
contains one
or the other.

FORMULAS
 

Sweet! Thanks, I thought I had to use ISERROR to keep the formula from
returning an error. This is going to save me alot of negating.

Thanks again,

Charles
Bob Phillips wrote:
DAD,

as an aside, rather than NOT(ISERROR, I use ISNUMBER, makes it a tad more
readable

=SUMPRODUCT((ISNUMBER(FIND("Fr",A1:A10)))*(ISNUMBE R(FIND("B",A1:A10))))

--
HTH

Bob Phillips

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

"Die_Another_Day" wrote in message
ups.com...
Nevermind, I was just making it too hard:
=SUMPRODUCT((NOT(ISERROR(FIND("Fr",A1:A10))))*(NOT (ISERROR(FIND("B",A1:A10)*
))))

Charles

Die_Another_Day wrote:
Bob Phillips, What a I missing to make this "SumProduct" formula work?

=SUMPRODUCT((NOT(ISERROR(FIND("Fr",A1:A10))))*(NOT (ISERROR(FIND("B",A1:A10))
)),(A1:A10))

Charles

Die_Another_Day wrote:

=SUM(IF(NOT(ISERROR(FIND("Fr",A1:A10))),IF(NOT(ISE RROR(FIND("B",A1:A10))),1)
))
then press Ctrl + Shift + Enter

Charles Chickering

HonorsOA wrote:
What formula counts how many cells contain two different criterias
i.e. if a
cell contains Fr as well as B I want it to count but not if it
contains one
or the other.

FORMULAS
 

if order isn't important

=COUNTIF(A1:A10,"*Fr*B*")+COUNTIF(A1:A10,"*B*Fr*")

if it is always Fr then B

=COUNTIF(A1:A10,"*Fr*B*")

In the first case, it could be inaccurate if you have BFRB as an
example.

--
Regards,
Tom Ogilvy


"HonorsOA" wrote:

What formula counts how many cells contain two different criterias i.e. if a
cell contains Fr as well as B I want it to count but not if it contains one
or the other.

FORMULAS
 

you could always get silly <g

=SUM(COUNTIF(A1:A10,{"*Fr*B*","*B*Fr*"})-COUNTIF(A1:A10,{"*B*Fr*B*","*Fr*B*F
r*"})*2)

--
HTH

Bob Phillips

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

"Tom Ogilvy" wrote in message
...
if order isn't important

=COUNTIF(A1:A10,"*Fr*B*")+COUNTIF(A1:A10,"*B*Fr*")

if it is always Fr then B

=COUNTIF(A1:A10,"*Fr*B*")

In the first case, it could be inaccurate if you have BFRB as an
example.

--
Regards,
Tom Ogilvy


"HonorsOA" wrote:

What formula counts how many cells contain two different criterias i.e.
if a
cell contains Fr as well as B I want it to count but not if it contains
one
or the other.