Simple Count of certain cells

  • Thread starter Thread starter JPDS
  • Start date Start date
J

JPDS

Whats the simplest way of counting how many times certain letters appear in a
list?
In cells A1:A2000, I have Ethnicity codes with singular letters A-Z, I would
like to count cetrain groups of letters say A,C,G,P,Z.

Thanks
 
Hi,

=SUM(COUNTIF(A1:A1000,{"A","C","G","P","Z"}))

--
Mike

When competing hypotheses are equal, adopt the hypothesis that introduces
the fewest assumptions while still sufficiently answering the question.
Occam''s razor (Abbrev)
 
Thats good, now would it work as a SUMPRODUCT function as I have other
qualifying criteria:

i.e. =SUMPRODUCT(--(INDIRECT($AW$3&"!$O$1:$O$6000")="XN01"),
SUMPRODUCT(INDIRECT($AW$3&"!$BL1:$BL6000"),{"A","B"})

Thanks
 
Maybe you can adapt this

=SUMPRODUCT((O1:O6000="XNO1")*(BL1:BL6000={"A","B"}))

--
Mike

When competing hypotheses are equal, adopt the hypothesis that introduces
the fewest assumptions while still sufficiently answering the question.
Occam''s razor (Abbrev)
 
Ive tried this but it doesnt seem to work:

=SUMPRODUCT(--(INDIRECT($AW$3&"!$O$1:$O$6000")="XN01"),--(INDIRECT($AW$3&"!$BL$1:$BL$6000")={"A","B"}),(INDIRECT($AW$3&"!$CI$1:$CI$6000")))
 
Thanks Jacob, i'm trying to use the 'range of qualifiers' as a criteria in
the following but when I use more than one, the following formula doesnt
appear to work:

=SUMPRODUCT(--(INDIRECT($AW$3&"!$O$1:$O$6000")="XN01"),--(INDIRECT($AW$3&"!$BL$1:$BL$6000")={"A","B"}),(INDIRECT($AW$3&"!$CI$1:$CI$6000")))

Thanks
 
Back
Top