=SUM((('Incident Details'!C1:C30=A15)+(A15="All"))*('Incident
Details'!D1:D30="No"))

Array entered worked for me.

--
Regards,
Tom Ogilvy



wrote in message
ups.com...
hi friends,

I need help in excel formula, if anyone can help me i will be highly
oblige. Problem is given as below

having data like
A B C D
1 abc 5 xyz Yes
2 bca 4 xyz No
3 fca 4 zyx No
4 hca 4 zyx Yes
5 bcl 4 yyy Yes
6 boa 4 zzz No

formula given by me is

{=SUM(('Incident Details'!C1:C30="A15")*('Incident
Details'!D1:D30="No"))}

It works well if i want to count occurance of "No" on the basis of
value in C column. Now I want that at the same time if i put "All" in
A15 which is not listed in C column is count occurance of "No" ignoring
filter given in formula for C column.

I am using this formula as array formula please help me.