help for text formula
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. |
help for text formula
=SUM(IF(A15="All",IF('Incident Details'!D1:D30="No",1,0),('Incident
Details'!C1:C30=A15)*('Incident Details'!D1:D30="No"))) -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) 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. |
help for text formula
=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. |
All times are GMT +1. The time now is 01:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com