re: complex query

My fault i didnt explain the question right

i want a formula that can count how many "joe bloggs" in coloumn "i" have the word "no call" in coloumn "E" or the word "Left" in coloum "E" or the Word "secondment in coloum "E" ...

i hope this is clearer

=SUMPRODUCT(--($I$1:$I$65000="Joe Bloggs"),--(($E$1:$E$65000)="Left"))+SUMPRODUCT(--($I$1:$I$65000="Joe Bloggs"),--(($E$1:$E$65000)="no call"))+SUMPRODUCT(--($I$1:$I$65000="Joe Bloggs"),--(($E$1:$E$65000)="secondment"))

Originally Posted by khalid79m

My fault i didnt explain the question right

i want a formula that can count how many "joe bloggs" in coloumn "i" have the word "no call" in coloumn "E" or the word "Left" in coloum "E" or the Word "secondment in coloum "E" ...

i hope this is clearer

Try...

=SUMPRODUCT(--($I$2:$I$100="Joe Bloggs"),--ISNUMBER(MATCH($E$2:$E$100,{"No Call","Left","Secondment"},0)))

or

=SUMPRODUCT(--($I$2:$I$100=L2),--ISNUMBER(MATCH($E$2:$E$100,$M$2:$M$4,0)))

...where L2 contains Joe Bloggs, and M2:M4 contains No Call, Left, and Secondment.

Hope this helps!