Simple Count of certain cells

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
 
M

Mike H

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)
 
J

JPDS

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
 
M

Mike H

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)
 
J

JPDS

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")))
 
J

JPDS

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top