Two criteria for CountIf?
 

I have two columns of cells that need to be evaluated and determine if the
criteria are met in order to be included in a count. The first column needs
to have something other than "Closed" and the second column and row should
contain something other than null/blank. Is there a simple way to include
both criteria in a single statement to allow a count of the rows?

Two criteria for CountIf?
 

=SUMPRODUCT(--(A1:A3<"closed"),--(NOT(ISBLANK(B1:B3))))

replace A1:A3 and B1:B3 with the appropriate ranges
DB_Bill wrote:
I have two columns of cells that need to be evaluated and determine if the
criteria are met in order to be included in a count. The first column needs
to have something other than "Closed" and the second column and row should
contain something other than null/blank. Is there a simple way to include
both criteria in a single statement to allow a count of the rows?

Two criteria for CountIf?
 

Try:

=SUMPRODUCT(--(A1:A100<"Closed"),--(B1:B100<""),--(B1:B100<" "))

Sumproduct must have a range (rather than a column)

HTH

"DB_Bill" wrote:

I have two columns of cells that need to be evaluated and determine if the
criteria are met in order to be included in a count. The first column needs
to have something other than "Closed" and the second column and row should
contain something other than null/blank. Is there a simple way to include
both criteria in a single statement to allow a count of the rows?

Two criteria for CountIf?
 

=SUMPRODUCT(--(A1:A100<"Closed"),--(TRIM(B1:B100)<""))

--
HTH

Bob Phillips

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

"DB_Bill" wrote in message
...
I have two columns of cells that need to be evaluated and determine if the
criteria are met in order to be included in a count. The first column
needs
to have something other than "Closed" and the second column and row should
contain something other than null/blank. Is there a simple way to include
both criteria in a single statement to allow a count of the rows?

Two criteria for CountIf?
 

Thank you for the reply.
I am not trying to achieve the product of a sum, rather get a count of rows
that have something other than the string "closed" and a value other than
blank in the second column. The Countif function seems to be similar to what
I need, however, it is only allowing me to specify criteria for one column at
a time. This is not allowing both criteria to be checked and validated.
An example would be like:
columnA columnB
closed 2100
closed
unknown 500
The last row would be counted and give a return of 1.

"Toppers" wrote:

Try:

=SUMPRODUCT(--(A1:A100<"Closed"),--(B1:B100<""),--(B1:B100<" "))

Sumproduct must have a range (rather than a column)

HTH

"DB_Bill" wrote:

I have two columns of cells that need to be evaluated and determine if the
criteria are met in order to be included in a count. The first column needs
to have something other than "Closed" and the second column and row should
contain something other than null/blank. Is there a simple way to include
both criteria in a single statement to allow a count of the rows?

Two criteria for CountIf?
 

Bill, the formula that Toppers gave you will return 1, its work.

as sumproduct return 1 and 1*1=1 it will sum how many 1's do you have in
your range, working like a countif.

hope it helps
Regard from Brazil
Marcelo

"DB_Bill" escreveu:

Thank you for the reply.
I am not trying to achieve the product of a sum, rather get a count of rows
that have something other than the string "closed" and a value other than
blank in the second column. The Countif function seems to be similar to what
I need, however, it is only allowing me to specify criteria for one column at
a time. This is not allowing both criteria to be checked and validated.
An example would be like:
columnA columnB
closed 2100
closed
unknown 500
The last row would be counted and give a return of 1.

"Toppers" wrote:

Try:

=SUMPRODUCT(--(A1:A100<"Closed"),--(B1:B100<""),--(B1:B100<" "))

Sumproduct must have a range (rather than a column)

HTH

"DB_Bill" wrote:

I have two columns of cells that need to be evaluated and determine if the
criteria are met in order to be included in a count. The first column needs
to have something other than "Closed" and the second column and row should
contain something other than null/blank. Is there a simple way to include
both criteria in a single statement to allow a count of the rows?

Two criteria for CountIf?
 

Thank you for the help. After looking at the replies more closely I realized
what was happening in the solutions given.

"Bob Phillips" wrote:

=SUMPRODUCT(--(A1:A100<"Closed"),--(TRIM(B1:B100)<""))

--
HTH

Bob Phillips

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

"DB_Bill" wrote in message
...
I have two columns of cells that need to be evaluated and determine if the
criteria are met in order to be included in a count. The first column
needs
to have something other than "Closed" and the second column and row should
contain something other than null/blank. Is there a simple way to include
both criteria in a single statement to allow a count of the rows?