3D Referencing

[Tikey]

I am using Office Pro 2021

I have what I guess is a fairly straight forward formula to count the number of unique dates in a range, for a particular month, in this example for August. The range of cells do not necessarily always all contain values.

=COUNT(UNIQUE($E$16:$E$20000/(MONTH($E$16:$E$20000)=8)))

That works fine, but I wish to use 3D referencing and apply this across a number of sheets

=COUNT(UNIQUE('AB1:AB5'!$E$16:$E$20000/(MONTH('AB1:AB5'!$E$16:$E$20000)=8)))

Now it just returns 0. I’m sure I have used the correct sheet range name notation ( 'AB1:AB5'! ) and have tried searching for an answer without success.

Can anyone offer a solution.

Thank you

 

[Tikey]

Thanks to you both for responding.

 

[Domenic]

In that case you won't be able to use Domenic's idea, as it cannot handle that many cells. I think you will have to do each sheet separately.

Hi Fluff,

Thanks for pointing that out. I really appreciate it.

Cheers!

 

[Fluff]

Thanks for pointing that out. I really appreciate it.

No problem.

Thanks to you both for responding.

Glad we could help

 

[Domenic]

Here's a possible alternative. Note, though, it uses the volatile functions OFFSET and INDIRECT. Also, you'll find it somewhat inefficient. Anyway, first list your sheet names in a vertical range of cells, let's say A2:A6. Then try the following formula . . .

=LET(shts,$A$2:$A$6,arr,N(OFFSET(INDIRECT("'"&INDEX(shts,INT(SEQUENCE(ROWS(shts)*20000,,0,1)/20000)+1)&"'!E16:E20000"),MOD(SEQUENCE(ROWS(shts)*20000,,0,1),20000),0,1)),COUNT(UNIQUE(FILTER(arr,MONTH(arr)=8))))

However, for the month of January, you'll need to use the following formula instead, in case you have empty/blank cells in your ranges . . .

=LET(shts,$A$2:$A$6,arr,N(OFFSET(INDIRECT("'"&INDEX(shts,INT(SEQUENCE(ROWS(shts)*20000,,0,1)/20000)+1)&"'!E16:E20000"),MOD(SEQUENCE(ROWS(shts)*20000,,0,1),20000),0,1)),COUNT(UNIQUE(FILTER(arr,(arr>0)*(MONTH(arr)=1)))))

Hope this helps!

 

[Tikey]

They both work, thank you. The inefficiency I suppose is shown in the time delay for the sheet to update when any change is made.

I have been editing some other formulas I use and have found that:

=SUMPRODUCT(--(FREQUENCY('AB1:AB5'!E16:E20000,'AB1:AB5'!E16:E20000)>0))

works, but in this form sums dates for the year. I have tried introducing (MONTH($E$16:$E$20000)=8) into the formula to obtain a monthly value without success, but I guess I'm doing something wrong.

 

[Domenic]

They both work, thank you.

You're very welcome!

The inefficiency I suppose is shown in the time delay for the sheet to update when any change is made.

Yes, that's right. The time delay can be very frustrating. For this reason, you may want to seek out an alternative solution instead.

I have been editing some other formulas I use and have found that:

=SUMPRODUCT(--(FREQUENCY('AB1:AB5'!E16:E20000,'AB1:AB5'!E16:E20000)>0))

works, but in this form sums dates for the year. I have tried introducing (MONTH($E$16:$E$20000)=8) into the formula to obtain a monthly value without success, but I guess I'm doing something wrong.

Initially, I did look into the use of FREQUENCY for a solution. However, like you, discovered that it won't accept such a construct with a 3D reference. But if you combine all of your data into one worksheet, you'll be able to use it that way. Otherwise, you may want to seek out a VBA solution instead.

 

[Tikey]

Thanks again for all your help.

 

[Domenic]

Cheers!