If statement with selected range

[honeyherrada]

Hello,

I'm trying to create an if statement that have 2 criterias. Here's what I've created:

=IF(COUNT(F2:F4)=('2007Open'!H2:H4),COUNT('2007Open'!B2:B4)=8,'2007Open'!H2:H4)

This statement is returning the #VALUE error.

I guess what I'm trying to do with this statement is to count how many values are not equal to 2007Open!H2:H4 and meets another criteria of 8.

Please help!

Thanks!

 

[honeyherrada]

Thanks Aladin,

Since SUMPRODUCT do not support wild characters...here's what my final formula looked like:

=SUMPRODUCT(1-ISNUMBER(MATCH('2007Closed'!$H$2:$H$6917,department_code,0)),--('2007Closed'!$B$2:$B$6917=26),--(LEFT('2007Closed'!$C$2:$C$6917,6)="200701"))