| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Convert Array Formulas to Regular Formulas | Excel Worksheet Functions | |||
| Convert Array Formulas to Regular Formulas | Excel Worksheet Functions | |||
| Counting # of Formulas in a column with formulas and entered data | Excel Worksheet Functions | |||
| CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions | |||
| AdvancedFilter on cells with formulas, returning values and not formulas | Excel Programming | |||
Linear Mode
