| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif Multiple column criteria with does not contain
Hi Masters!
Help Excel 2003: 3 columns data. (F, G, AA) The last 2 columns are numeric data. 1st one is not numeric data but names. (i.e. Carl, Ron etc...) This row contains totals that I want to ignore. The only common parameter in the totals is the symbol $ but is not always in the same position (i.e. Carl $, Ron $ Total etc...) I am trying to count the number of cases that follow the criteria G0, AA<=0 and ignore the Rows that contains $ in F. I am trying to use the formula bellow without success. Any idea even more simply is appreciated! SUMPRODUCT(--(IF(ISNUMBER(SEARCH("*$*",F17:F194,1)),"No","1"))= 1,--(G17:G1940),--(AA17:AA194<=0)) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif Multiple column criteria with does not contain
Nick wrote:
Hi Masters! Help Excel 2003: 3 columns data. (F, G, AA) The last 2 columns are numeric data. 1st one is not numeric data but names. (i.e. Carl, Ron etc...) This row contains totals that I want to ignore. The only common parameter in the totals is the symbol $ but is not always in the same position (i.e. Carl $, Ron $ Total etc...) I am trying to count the number of cases that follow the criteria G0, AA<=0 and ignore the Rows that contains $ in F. I am trying to use the formula bellow without success. Any idea even more simply is appreciated! SUMPRODUCT(--(IF(ISNUMBER(SEARCH("*$*",F17:F194,1)),"No","1"))= 1,--(G17:G1940),--(AA17:AA194<=0)) Try this: =SUMPRODUCT(--ISERROR(SEARCH("$",A2)),--(G17:G1940),--(AA17:AA194<=0)) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif Multiple column criteria with does not contain
Nick wrote:
Hi Masters! Help Excel 2003: 3 columns data. (F, G, AA) The last 2 columns are numeric data. 1st one is not numeric data but names. (i.e. Carl, Ron etc...) This row contains totals that I want to ignore. The only common parameter in the totals is the symbol $ but is not always in the same position (i.e. Carl $, Ron $ Total etc...) I am trying to count the number of cases that follow the criteria G0, AA<=0 and ignore the Rows that contains $ in F. I am trying to use the formula bellow without success. Any idea even more simply is appreciated! SUMPRODUCT(--(IF(ISNUMBER(SEARCH("*$*",F17:F194,1)),"No","1"))= 1,--(G17:G1940),--(AA17:AA194<=0)) Sorry, forgot to adjust the range: =SUMPRODUCT(--ISERROR(SEARCH("$",F17:F194)),--(G17:G1940),--(AA17:AA194<=0)) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif Multiple column criteria with does not contain
Hi Glenn! Beautiful! It works perfect.
I have being asked to put in the cell adjacent the total sum of column G as well for this same criteria. Will SUMPRODUCT works? Or what would be the best simple formula? Thanks again! Nick "Glenn" wrote: Nick wrote: Hi Masters! Help Excel 2003: 3 columns data. (F, G, AA) The last 2 columns are numeric data. 1st one is not numeric data but names. (i.e. Carl, Ron etc...) This row contains totals that I want to ignore. The only common parameter in the totals is the symbol $ but is not always in the same position (i.e. Carl $, Ron $ Total etc...) I am trying to count the number of cases that follow the criteria G0, AA<=0 and ignore the Rows that contains $ in F. I am trying to use the formula bellow without success. Any idea even more simply is appreciated! SUMPRODUCT(--(IF(ISNUMBER(SEARCH("*$*",F17:F194,1)),"No","1"))= 1,--(G17:G1940),--(AA17:AA194<=0)) Sorry, forgot to adjust the range: =SUMPRODUCT(--ISERROR(SEARCH("$",F17:F194)),--(G17:G1940),--(AA17:AA194<=0)) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif Multiple column criteria with does not contain
So, if you are saying you want the sum of column G when:
There is not a "$" in column F Column G is greater than 0 Column AA is less than 0 =SUMPRODUCT(--ISERROR(SEARCH("$",F17:F194)),--(G17:G1940),--(AA17:AA194<=0),G17:G194) Nick wrote: Hi Glenn! Beautiful! It works perfect. I have being asked to put in the cell adjacent the total sum of column G as well for this same criteria. Will SUMPRODUCT works? Or what would be the best simple formula? Thanks again! Nick "Glenn" wrote: Nick wrote: Hi Masters! Help Excel 2003: 3 columns data. (F, G, AA) The last 2 columns are numeric data. 1st one is not numeric data but names. (i.e. Carl, Ron etc...) This row contains totals that I want to ignore. The only common parameter in the totals is the symbol $ but is not always in the same position (i.e. Carl $, Ron $ Total etc...) I am trying to count the number of cases that follow the criteria G0, AA<=0 and ignore the Rows that contains $ in F. I am trying to use the formula bellow without success. Any idea even more simply is appreciated! SUMPRODUCT(--(IF(ISNUMBER(SEARCH("*$*",F17:F194,1)),"No","1"))= 1,--(G17:G1940),--(AA17:AA194<=0)) Sorry, forgot to adjust the range: =SUMPRODUCT(--ISERROR(SEARCH("$",F17:F194)),--(G17:G1940),--(AA17:AA194<=0)) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using Countif with multiple criteria in the same column. | Excel Discussion (Misc queries) | |||
| Countif function with multiple column criteria? | Excel Worksheet Functions | |||
| COUNTIF: 2 criteria: Date Range Column & Text Column | Excel Worksheet Functions | |||
| Nesting COUNTIF for multiple criteria in multiple columns | Excel Worksheet Functions | |||
| Countif multiple criteria within the same column | Excel Discussion (Misc queries) | |||
Linear Mode
