Countif – Multiple column criteria – with “does not containâ€

  • Thread starter Thread starter Nick
  • Start date Start date
N

Nick

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 G>0, 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:G194>0),--(AA17:AA194<=0))
 
Nick said:
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 G>0, 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:G194>0),--(AA17:AA194<=0))
Click to expand...



Try this:


=SUMPRODUCT(--ISERROR(SEARCH("$",A2)),--(G17:G194>0),--(AA17:AA194<=0))
 
Nick said:
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 G>0, 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:G194>0),--(AA17:AA194<=0))
Click to expand...




Sorry, forgot to adjust the range:

=SUMPRODUCT(--ISERROR(SEARCH("$",F17:F194)),--(G17:G194>0),--(AA17:AA194<=0))
 
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
 
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
 
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:G194>0),--(AA17:AA194<=0),G17:G194)
 
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:G194>0),--(AA17:AA194<=0),G17:G194)
 
Back
Top