Sum IF Frequency blank cells calculation

[Weeble]

I'm trying out this formula

{=SUM(IF(FREQUENCY(IF(Blad2!$N:$N="<"">",Blad2!$B:$B),IF(Blad2!$N:$N="<"">",Blad2!$B:$B))>0,1))}

To try and get all the unqie values in column B when column N is blank.
I have similar code for when N is not blank, it it works fine.

{=SUM(IF(FREQUENCY(IF(Blad2!$N:$N="IS",Blad2!$B:$B),IF(Blad2!$N:$N="IS",Blad2!$B:$B))>0,1))}

Anyone able to tell me another way I can do this?

 

[Marcelo Branco]

I'm assuming column B contains numbers, not texts. If so try something like this

=SUM(IF(FREQUENCY(IF(Blad2!$N$2:$N$1000="",IF(Blad2!$B$2:$B$1000<>"",Blad2!$B$2:$B$1000)),IF(Blad2!$N$2:$N$1000="",IF(Blad2!$B$2:$B$1000<>"",Blad2!$B$2:$B$1000))),1))
Ctrl+Shift+enter

Remark: for the sake of performance avoid references to entire columns like $N:$N in array formulas. Adjust the ranges to suit the expected/likely/desired number of rows.

M.

 

[Weeble]

It still does not count the blank row on my sheet.

A	B	C	D	E	F	G	H	I	J	K	L	M	N
2017-09-11	8313	.	.	.	.	.	.	.	.	.	.	.
2017-09-12	8398	.	.	.	.	.	.	.	.	.	.	.	IS
2017-09-12	8397	.	.	.	.	.	.	.	.	.	.	.	IS

<tbody>
</tbody>

I have a summary field that sais
Amount of unique "IS" based of the B column
Amount of unique " " basesd of the B column

So the summary should pickup that I have 1 from B.

Thank you very much for the information on Array formulas. A followup question. Even if my rows can add up to 15 000, it's still better have it ranged? The thing that i'm afraid of is if I DL a new query and it exceeds 15k rows, someone will not pay attention to it and miss valuable information. Would it be better to try and VBA the entire thing?

 

[Marcelo Branco]

Try this - observe that i used a big range 2:30000

=SUM(IF(FREQUENCY(IF(Blad2!$N$2:$N$30000="",IF(Blad2!$B$2:$B$30000<>"",Blad2!$B$2:$B$30000)),IF(Blad2!$N$2:$N$30000="",IF(Blad2!$B$2:$B$30000<>"",Blad2!$B$2:$B$30000))),1))
Ctrl+Shift+Enter

M.