Create named range for chart by excluding certain rows

**bdas** · 10-01-2015, 11:26 AM

I need to create a named range to be used as chart data. But the named range should not include rows which contain specific text. An example given below:
COL A COL B

APPLE 200
ORANGE 300
MANGO 100
PINAPPLE 200
APPLE 350
MANGO 500

For example, the named range (containing A1:B6) should exclude all rows where the text in column A contains the name APPLE. Difficult to hide the rows as the data will be updated daily.

Any help would be appreciated.

**Kaper** · 10-02-2015, 07:23 AM

Try for example such names:

Formula:

Please Login or Register  to view this content.

then write your data somewhere in columns A and B (within first 50 rows) and select for instannce D5:D14 and (having this whole range selected) insert array formula*

Formula:

Please Login or Register  to view this content.

and similar for whole range E5:E14 (also array formula)

Formula:

Please Login or Register  to view this content.

see attached file

*...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.