To Call All Value Exclude value consist of special words

**cyee** · 06-27-2013, 04:14 AM

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.

Thanks in advance

**XOR LX** · 06-27-2013, 04:46 AM

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

**cyee** · 06-27-2013, 05:30 AM

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.

**XOR LX** · 06-27-2013, 05:40 AM

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

**cyee** · 06-27-2013, 05:52 AM

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.

**XOR LX** · 06-27-2013, 06:12 AM

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