Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested formula to search a text string and return specific text | Excel Worksheet Functions | |||
Formula Text String: Formatting Text and Numbers? | Excel Discussion (Misc queries) | |||
Excel:Get concatenated text to be recognised as formula not text? | Excel Discussion (Misc queries) | |||
Formula to count text and alert me if a text appears more than twi | Excel Discussion (Misc queries) | |||
match cell text with text in formula | Excel Worksheet Functions |