Make INDIRECT reference dynamic

[ollyhughes1982]

Hi,

I have a number of very similar INDIRECT formulas in a spreadsheet, example of one below:

=SUM(COUNTIF(INDIRECT({"L4","T4","AB4","AJ4","AR4","AZ4","BH4","BP4","BX4","CF4","CQ4"}),"No"))

I'm using the INDIRECT function as I want to count if what's in non-adjacent cells meets the criteria "No". These work, however, the reference aren't dynamic. Is there a better way that I could do this so that whenever I happen to insert new columns in spreadsheet, I don't have to go in and update all of these references? perhaps with some form of COUNTIF / COUNTIFS?

Thanks in advance.

Olly.

 

[JEC]

Try this

=SUM(--(VSTACK(L4,T4,AB4)="No"))

 

[ollyhughes1982]

Try this

=SUM(--(VSTACK(L4,T4,AB4)="No"))

Good idea. I've used VSTACK before, not sure why didn't think of using that!. Thanks.

 

[ollyhughes1982]

Good idea. I've used VSTACK before, not sure why didn't think of using that!. Thanks.

What does the "--" do?

 

[JEC]

It converts True/False to a number. So 1/0. This way you can use sum

"--" is actually two times minus 1. Double minus = positive : )

 

[ollyhughes1982]

It converts True/False to a number. So 1/0. This way you can use sum

"--" is actually two times minus 1. Double minus = positive : )

I'm trying this but getting a message saying the formula is missing something and it won't let me enter it. Do you know what's going wrong with it, please?

=SUM(COUNTIF(--VSTACK(I4,Q4,Y4,AG4,AO4,AW4,BE4,BM4,BU4,CC4,CN4),1)

So, I am trying to count wherever the cells I4, Q4, Y4, AG4, AO4, AW4, BE4, BM4, BU4, CC4 and CN4 are =1 and then sum these together.

Thanks again.

 

[Fluff]

Are all those cells just 1 or 0?

 

[ollyhughes1982]

Are all those cells just 1 or 0?

The actual values are either * (for a break and dish a * is entered) or null, so essentially true or false. I have some other where there are three options

 

[Fluff]

If they are either * or null why are your trying to do a countifs equal to 1

 

[ollyhughes1982]

If they are either * or null why are your trying to do a countifs equal to 1

Yes, it should be looking for *, I just quickly put that in for this example and realised it was *, not 1, for this example afterwards. That doesn't affect the formula not working though, it'd work, but just always give 0?