counta and or if

  • Thread starter Thread starter MB
  • Start date Start date
M

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?
 
=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"}))
 
Dave Peterson said:
=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"}))
Click to expand...

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?
 
Teethless mama said:
=SUMPRODUCT(--ISNA(MATCH(A1:A7,{"RDO","S","V"},0))*(A1:A7<>""))
Click to expand...


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
 
Back
Top