Count if on Single Criteria with multiply conditions

[Guest]

Am trying to do a countif when the criteria can be one of many from a list

I am currently using

=(COUNTIF(INDEX(A1:j12,,2),Sheet2!$C$3))+(COUNTIF(INDEX(A1:J12,,2),Sheet2!$C$4))+(COUNTIF(INDEX(A1:J12,,2),Sheet2!$C$5))+(COUNTIF(INDEX(A1:J12,,2),Sheet2!$C$6))+(COUNTIF(INDEX(A1:J12,,2),Sheet2!$C$7))+(COUNTIF(INDEX(A1:J12,,2),Sheet2!$C$8))+(COUNTIF(INDEX(A1:J12,,2),Sheet2!$C$9))

this is only one of around 100 calculations of the same format within this
spreadsheet as you can see very messy is there a way of been able to do a
countif if the criteria is part of a list eg

=COUNTIF(INDEX(A1:j12,,2),Sheet2!$C$3:$C$9)

i know this formula doesnot work and will not work but is there some way of
making it so it counts these values

any way around this would be great


Thanks Damien

 

[Biff]

Try this:

=SUMPRODUCT(--(ISNUMBER(MATCH(INDEX(A1:J12,,2),Sheet2!C3:C9,0))))

Biff