counta and or if

[MB]

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(a1:a7)
will count the non-empty cells

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

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

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

 

[Teethless mama]

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

 

[Teethless mama]

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

 

[MB]

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

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

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

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

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?

 

[MB]

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

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?

 

[Ashish Mathur]

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