Referencing A Non Consecutive Range?

[RayportingMonkey]

I have a single column of data AJ11:AJ58 which contains a formula that
results in OPEN or CLOSED. I need to reference this column in multiple
formulas as well as in the X Axis of a chart.

Not only do these values change, but they are also not necessarialy
consecutive.

For example, one pass results in a range of OPEN cells (which is what I need
to reference) of AJ25:AJ52 and another common pass results in
AJ11:AJ12,AJ21:AJ58.

Is there a way to name this dynamic range so I can reference it as described?

Obviously, the big problem is ranges like AJ11:AJ12,AJ21:AJ58. I can't
figure out how to use a range like this in an IF statement. I also tried
naming those addresses as a range and then named a range that referenced it,
but that didn't work...

I'm running out of options and looking at a deadline - Can you say "Panic"?

Thanks for your help!
Ray

 

[Ragdyer]

Referenced in what way?

The number of cells displaying "Open":
=COUNTIF(AJ11:AJ58,"Open")

The total of values in an adjoining column:

=SUMIF(AJ11:AJ58,"Open",AK11:AK58)

Just what exactly are you looking to accomplish?

 

[Frank Pytel]

RayportingMonkey;

You need to provide more data. You don't have a single column, You have a
calculated column so you have at least three.

Calculating to open or closed. You could add another column that says

=If(a1="Open","open","")

Drag it down the column relative to the results and this will give you only
"open" and then you can =countif() or =lookup(). You could also try the
following

=IF(LOOKUP(G1,A1:A12)="open",COUNTIF(A1:A12,"open"),"")

Where G1 contains the default information that you are looking for.

I hope this helps

God Bless

Frank Pytel

 

[RayportingMonkey]

Have you ever had one of those moments when you realize that you have over
analyzed and probably complicated the situation?

I solved this by naming a three ranges.

Range 1 contains an IF statement that picks range 2 or 3

Range 2 is my consecutive range

Range 3 is a seperate location on my worksheet where I made a new
consecutive range that skips the items that made up the break in my
nonconsecutive range.

All is good and I am still employed!

Thanks for your assistance!