To Call All Value Exclude value consist of special words

1. To Call All Value Exclude value consist of special words

Hi All,

I have below formulas to call up all value in column F.
When the formulas do the call up, is it possible to exclude value consist of CW, AWP, TMP?

My Column F will look like below:-
B5 - 3
CW - E
A2 - 3
B4 - 1
CW - D
A2 - 2
B2 - 2
CW - D
AWP - C
CW - E

``Please Login or Register  to view this content.``
Hope some one able to help me on the code.

2. Re: To Call All Value Exclude value consist of special words

Hi,

=IF(\$A5>\$C\$5, "", INDEX('Tele-Visit Report'!F\$1:F\$1000, SMALL(IF(('Tele-Visit Report'!\$H\$1:\$H\$1000=\$B\$5)*(ISERROR(SEARCH("CW",'Tele-Visit Report'!\$F\$1:\$F\$1000))*(ISERROR(SEARCH("AWP",'Tele-Visit Report'!\$F\$1:\$F\$1000))*(ISERROR(SEARCH("TMP",'Tele-Visit Report'!\$F\$1:\$F\$1000))))),ROW('Tele-Visit Report'!F\$1:F\$1000), ""), ROWS(\$A\$1:\$A1))))

Regards

3. Re: To Call All Value Exclude value consist of special words

Hi,

Thanks you very much for your fomulas. It work very well.
I have modify it to fix all column. It work really good but found there is an error of #NUM! which i was unable to fix it.

Would appreciate if you could help.

I attached my worksheet here for easy reference.

4. Re: To Call All Value Exclude value consist of special words

Hi,

Please explain in words to me the function of the part of the formula in C5 highlighted in red below:

COUNTIFS('Tele-Visit Report'!B:B, "<>", 'Tele-Visit Report'!H:H, B5)

I do not recoginse the use or meaning of "<>" in this case - not equal to what?

Regards

5. Re: To Call All Value Exclude value consist of special words

Actually innitially i need to have the Total amount of person. But I dont need this anymore in this worksheet.
So you may delete it.

6. Re: To Call All Value Exclude value consist of special words

Two of your six entries in column F of the Tele-Visit Report sheet contain "CW", which you asked to be excluded from the results. #NUM! in this case simpy means that, after returning the matching four entries, there is no fifth, sixth, etc. to be put in these rows.

You can either mask this with an IFERROR, or, since it appears that whoever set this up for you is using cell C5 for this purpose (hence why I asked you about the formula in this cell), you would need to amend that to include these new criteria, i.e. in C5:

=SUMPRODUCT(('Tele-Visit Report'!\$H\$1:\$H\$1000=\$B\$5)*(ISERROR(SEARCH("CW",'Tele-Visit Report'!\$F\$1:\$F\$1000))*(ISERROR(SEARCH("AWP",'Tele-Visit Report'!\$F\$1:\$F\$1000))*(ISERROR(SEARCH("TMP",'Tele-Visit Report'!\$F\$1:\$F\$1000))))))

Regards

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1