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:J 12,,2),Sheet2!$C$5))+(COUNTIF(INDEX(A1:J12,,2),She et2!$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

Try this:

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

Biff

"dpgraves" wrote in message
...
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:J 12,,2),Sheet2!$C$5))+(COUNTIF(INDEX(A1:J12,,2),She et2!$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