# of unique entries across 2 tabs (excluding duplicates and empty cells)

[virtuosok]

Hi,
This one is a bit complicated... I currently have a working array formula to look up the # of unique dates on one single tab in line with pre-defined criteria:
{=SUM(IF(FREQUENCY(IF('Tab one'!$E$13:$E$50000=B4,'Tab one'!$F$13:$F$50000),'Tab one'!$F$13:$F$50000),1))}

Can I have this formula working across two tabs (let's say another one will be called Tab two), looking into exactly the same ranges and following the same logic? As an option, totally different formula will work just as well, as long as it returns valid results.
Please help

 

[virtuosok]

...or maybe I should do something along the lines of
=countunique(iferror(filter($F$13:$F$50000, $E$13:E=B4)))
...however how to make sure it works across 2 tabs?

Another type of formula would be =SUMPRODUCT(--(FREQUENCY(Sheet1:Sheet2!... but still cannot figure out the exact syntax...

 

[Eric W]

How about:

Book1
	A	B	C	D	E	F
1
2
3
4		a
5		4
6
7
8
9
10
11
12
13					a	2-Sep
14					a	3-Sep
15					b	5-Sep
16
17
Tab one

Cell Formulas
Range	Formula
B5	B5	=SUM(IF(FREQUENCY(IF('Tab one'!$E$13:$E$50000=B4,'Tab one'!$F$13:$F$50000),ROW(INDIRECT("1:60000")))+FREQUENCY(IF('Tab two'!$E$13:$E$50000=B4,'Tab two'!$F$13:$F$50000),ROW(INDIRECT("1:60000"))),1))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[virtuosok]

Goodness, thanks a million Eric W! This works like a charm! Appreciate your help so much!!!
Now that you helped me how to figure out the # of unique dates across two tabs, would you mind reviewing my formula for the first - and the last - dates in either tab, for a specific criterion? My current formulas which only take into account Tab one, look as follows, and they work well but I would need them to scan both tabs (same ranges):
=MIN(IF('Tab one'!$E$13:$E$50000=B2,'Tab one'!$F$13:$F$50000))
=MAX(IF('Tab one'!$E$13:$E$50000=B2,'Tab one'!$F$13:$F$50000))

 

[Eric W]

It should just be:

=MAX(IF('Tab one'!$E$13:$E$50000=B2,'Tab one'!$F$13:$F$50000),IF('Tab two'!$E$13:$E$50000=B2,'Tab two'!$F$13:$F$50000))

with CSE.

 

[virtuosok]

It should just be:

=MAX(IF('Tab one'!$E$13:$E$50000=B2,'Tab one'!$F$13:$F$50000),IF('Tab two'!$E$13:$E$50000=B2,'Tab two'!$F$13:$F$50000))

with CSE.

Works perfectly! Thanks a lot, you made my week!!!

 

[virtuosok]

Hi Eric W (and everyone )
Can you tell me please what the bit in red font does in human understandable terms? I am taking the formula from message #3 in this thread. Why exactly 60000?

=SUM(IF(FREQUENCY(IF('Tab one'!$E$13:$E$50000=B4,'Tab one'!$F$13:$F$50000),ROW(INDIRECT("1:60000")))+FREQUENCY(IF('Tab two'!$E$13:$E$50000=B4,'Tab two'!$F$13:$F$50000),ROW(INDIRECT("1:60000"))),1))

 

[Eric W]

60000 was just a convenient number. You were looking at dates, and Excel stores dates as numbers. January 1, 1900 is 1, today (10/7/2020) is 44111, and 60000 is 4/8/2064. I made the assumption that your dates would be no more than 44 years in the future. If the data you're looking at were just random numbers or text, the formula would be more complicated.

 

[virtuosok]

Thanks Eric W, this explains (and yes, 44 years is more than enough )
The formula works in all instances except for one in my specific case - and I'm trying to figure out what's wrong with that one. Then my second attempt would be to streamline the blue bits:
=SUM(IF(FREQUENCY(IF('Tab one'!$E$13:$E$50000=B4,'Tab one'!$F$13:$F$50000),ROW(INDIRECT("1:60000")))+FREQUENCY(IF('Tab two'!$E$13:$E$50000=B4,'Tab two'!$F$13:$F$50000),ROW(INDIRECT("1:60000"))),1))

I tried to replace the blue bits with
E:E
$E$13:E
...and other variants of spellings, but consistently failed. Essentially I need the formula to take into account all entries from E13 downward, and that range contains only dates.

 

[Eric W]

Since there are 1,048,576 rows in an Excel 2016 sheet, you can change it to:

=SUM(IF(FREQUENCY(IF('Tab one'!$E$13:$E$1048576 =B4,'Tab one'!$F$13:$F$1048576),ROW(INDIRECT("1:60000")))+FREQUENCY(IF('Tab two'!$E$13:$E$1048576=B4,'Tab two'!$F$13:$F$1048576),ROW(INDIRECT("1:60000"))),1))

or if you know that E1:E12 will never match B4, you can use the whole column references:

=SUM(IF(FREQUENCY(IF('Tab one'!$E:$E=B4,'Tab one'!$F:$F),ROW(INDIRECT("1:60000")))+FREQUENCY(IF('Tab two'!$E:$E=B4,'Tab two'!$F:$F),ROW(INDIRECT("1:60000"))),1))

But I warn you that using whole column references can slow down your sheet a lot. If you have 100,000 rows you need to check, then this formula will still do 10 times the work that it has to. If you notice slow processing, we can try to come up with a dynamic range formula that looks for the last used row and incorporate that.