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

 

[Domenic]

Try . . .

=SUM(IF(MONTH(UNIQUE(FILTERXML("<root><item>"&SUBSTITUTE(TEXTJOIN(",",TRUE,'AB1:AB5'!E16:E20000),",","</item><item>")&"</item></root>","//item")))=8,1))

Hope this helps!

 

[Tikey]

Thanks for your reply. Unfortunately I don't understand what root and item are, do I have to substitute them for some value ??

 

[Fluff]

Roughly how many rows of data do you have on those sheets & is it 5 sheets?

 

[Tikey]

There are 5 sheets and I want the answer from this formula on a summary sheet. There can be up to 20000 rows

 

[Domenic]

Thanks for your reply. Unfortunately I don't understand what root and item are, do I have to substitute them for some value ??

No, there's no need to do any substitution. It's simply used to format the string in XML format.

 

[Tikey]

I'm showing my lack of knowledge, I still don't understand

 

[Fluff]

There can be up to 20000 rows

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.

 

[Tikey]

I guess you are saying that COUNT, UNIQUE will not work across sheets. Is there another formula that would be better to use and would work ?

 

[Fluff]

Is there another formula that would be better to use and would work ?

Not that I know of.