Trying to make COUNTIFS, UNIQUE, and INDIRECT play nicely together

[oenleunc]

Howdy,

I'm trying to (ultimately) create a visualization in Spotfire using a range full of dates (AF2:MX100), which Spotfire doesn't care for. That's fine, I just need to count the number of unique dates per row for given two-week period. That period would be the dates specified in E1:AE1 (26 columns denoting every other week).

Luckily I'm using Excel 365 so I can use the UNIQUE function (holy smokes does that make it easier). However, Excel is not appreciating my formula, and after an embarrassingly long time searching for something to help, I'm finally asking for help.

=COUNTIFS(UNIQUE(AF2:MX2,FALSE,FALSE))<INDIRECT(F1), (UNIQUE(AF2:MX2,FALSE,FALSE))>=INDIRECT(E1)

Where AF2:MX2 is the range of dates I'd like to count distinct values for, and I'd like the distinct values to be between dates E1 and F1.

 

[Dave Patton]

It would help if you posted an example with the forum's tool named XL2BB, Please also show the expected result.

I had one quick thought.

Unique.xlsm
	A	B	C	D	E	F
1					1-Dec-22	31-Dec-22
2	15-Nov-22	12-Dec-22	12-Dec-22	15-Dec-22	15-Dec-22	30-Dec-22
3
4		3
8d

Cell Formulas
Range	Formula
B4	B4	=LET(x,UNIQUE(A2:F2,1,0),SUM((x>E1)*(x<F1)))

 

[oenleunc]

Yup that works. I've never seen the "let" function before. I would love a quick explanation of the formula if you're willing.

I can't download XL2BB because work computer

Thank you!!

 

[Dave Patton]

I would love a quick explanation of the formula if you're willing.

try Excel's help for the Let function.
There are lots of good examples on this forum.

You can also try Excel Formulas Formula Evaluate