help with Countifs and array

[spenny32]

Hi, newbie needing some help... (see attachment)

I have 4 columns of data I need to use at the moment... I am looking to do the following:
- if a row contains "999" or 101, 102, 104, 105, 107, or 108 in any of the 4 columns (F:I), I want the empty column tot he right to show "1"

column H & I would be exactly those numbers, where F & G may contain something like xyz999

Ive started a formula that will work for counting the 999, but cant get the rest... and not sure how to make it count as only "1" and not 3 if there are multiple 999/or 102... in the row

[PeteABC123]

Create a helper list with the values you're looking to see if they're in your range. In this formula; it's a named range called LIST


Place this into J15:

Please Login or Register  to view this content.

and copy down.

Pete

[spenny32]

Thanks Pete!

So for cell G15 (TAH9999), this is also a default code... usually they are 999 but can vary... can I add to this formula to make it pick up any instances of 999?

[PeteABC123]

Sure spenny32. I don't see why not. Add TAH9999 to your LIST, if I catch the drift.
Pete

[spenny32]

Hi Pete, this would work, however the amount of potential haulers would make the helper list quite a task to make for 8 different plants... is there a way to use a wildcard in there or some other variation to pick up only the 999 portion?

[PeteABC123]

Try this:

Please Login or Register  to view this content.

Pete

[spenny32]

perfect!! thanks so much for the help, this is working

[PeteABC123]

Great! If it's all done and happy, feel free to mark this thread as SOLVED.

Glad to help.
Pete

[spenny32]

so close... one more hiccup

attached image to show formula - it is counting the *999* but if there is one of the defaults (ie 102) it will not calculate in the helper row. Tried just making it a COUNTIFS and using an array but that did not seem to work either. Any thoughts?

[PeteABC123]

You'll still need to create the named range somewhere in your worksheet, which I referred to as LIST, and have the values 102 103 etc. represented there.

If you've done that spenny32 and it's still got the hiccups, can you post a sanitized worksheet archetype of this peevish rascal?
Tanks.

Pete

[spenny32]

Thanks so much for the help! see attached (i did create the named range "default" to the far right column)

[PeteABC123]

Those varmints! Hi: You'll need to include column "I" in your formula.
Try this in J2:

Please Login or Register  to view this content.

Copy down.

Cheerio pip pip

Pete

[spenny32]

Heavens to Murgatroyd... thank you!