counta and or if
 

I am trying to COUNTA rows of 7 cells that contain data. i do not want the
cells counted if they meet one or more conditions. For example:

count non blank cells that do not include one of the following text:

RDO
S
V

Can someone help. Have been trying for several hours and it is not sinking
in?

counta and or if
 

=counta(a1:a7)
will count the non-empty cells

=countif(a1:a7,"rdo")+countif(a1:a7,"s")+countif(a 1:a7,"v")
will count the rdo/s/v's.

so...
=counta(a1:a7)-(countif(a1:a7,"rdo")+countif(a1:a7,"s")+countif(a 1:a7,"v"))

or a version easier to update:
=counta(a1:a7)-sum(countif(a1:a7,{"rdo","s","v"}))



MB wrote:

I am trying to COUNTA rows of 7 cells that contain data. i do not want the
cells counted if they meet one or more conditions. For example:

count non blank cells that do not include one of the following text:

RDO
S
V

Can someone help. Have been trying for several hours and it is not sinking
in?

--

Dave Peterson

counta and or if
 

=SUMPRODUCT(--ISNA(MATCH(A1:A7,{"RDO","S","V"},0))*(A1:A7<""))


"MB" wrote:

I am trying to COUNTA rows of 7 cells that contain data. i do not want the
cells counted if they meet one or more conditions. For example:

count non blank cells that do not include one of the following text:

RDO
S
V

Can someone help. Have been trying for several hours and it is not sinking
in?

counta and or if
 

=SUMPRODUCT(ISNA(MATCH(A1:A7,{"RDO","S","V"},0))*( A1:A7<""))


"Teethless mama" wrote:

=SUMPRODUCT(--ISNA(MATCH(A1:A7,{"RDO","S","V"},0))*(A1:A7<""))


"MB" wrote:

I am trying to COUNTA rows of 7 cells that contain data. i do not want the
cells counted if they meet one or more conditions. For example:

count non blank cells that do not include one of the following text:

RDO
S
V

Can someone help. Have been trying for several hours and it is not sinking
in?

counta and or if
 

"Dave Peterson" wrote:

=counta(a1:a7)
will count the non-empty cells

=countif(a1:a7,"rdo")+countif(a1:a7,"s")+countif(a 1:a7,"v")
will count the rdo/s/v's.

so...
=counta(a1:a7)-(countif(a1:a7,"rdo")+countif(a1:a7,"s")+countif(a 1:a7,"v"))

or a version easier to update:
=counta(a1:a7)-sum(countif(a1:a7,{"rdo","s","v"}))



MB wrote:

I am trying to COUNTA rows of 7 cells that contain data. i do not want the
cells counted if they meet one or more conditions. For example:

count non blank cells that do not include one of the following text:

RDO
S
V

Can someone help. Have been trying for several hours and it is not sinking
in?

--

Dave Peterson


It worked. So, now, this formula has to be copied down serveral rows, and
perhaps I might periodically want to change the list. Could I not set a name
definition?

counta and or if
 

"Teethless mama" wrote:

=SUMPRODUCT(--ISNA(MATCH(A1:A7,{"RDO","S","V"},0))*(A1:A7<""))


"MB" wrote:

I am trying to COUNTA rows of 7 cells that contain data. i do not want the
cells counted if they meet one or more conditions. For example:

count non blank cells that do not include one of the following text:

RDO
S
V

Can someone help. Have been trying for several hours and it is not sinking
in?


It worked. So, now, this formula has to be copied down serveral rows, and
perhaps I might periodically want to change the list. Could I not set a name
definition?

counta and or if
 

Hi, you may also want to try using the DCOUNTA() function. Assuming that
the title of the column is Header, enter the following in range A17:D18
(cell D18 is blank right now). In cell D18, enter the following formula
=DCOUNTA($A$2:$A$11,D17,A17:C18)

Header Header Header Header
<RDO <S <V--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"MB" wrote in message
...
I am trying to COUNTA rows of 7 cells that contain data. i do not want
the
cells counted if they meet one or more conditions. For example:

count non blank cells that do not include one of the following text:

RDO
S
V

Can someone help. Have been trying for several hours and it is not
sinking
in?