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(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

"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?

=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?

=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?

"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?

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?