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.
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]2017-09-11[/TD]
[TD]8313[/TD]
[TD].[/TD]
[TD].[/TD]
[TD].[/TD]
[TD].[/TD]
[TD].[/TD]
[TD].[/TD]
[TD].[/TD]
[TD].[/TD]
[TD].[/TD]
[TD].[/TD]
[TD].[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2017-09-12[/TD]
[TD]8398[/TD]
[TD].[/TD]
[TD].[/TD]
[TD].[/TD]
[TD].[/TD]
[TD].[/TD]
[TD].[/TD]
[TD].[/TD]
[TD].[/TD]
[TD].[/TD]
[TD].[/TD]
[TD].[/TD]
[TD]IS[/TD]
[/TR]
[TR]
[TD]2017-09-12[/TD]
[TD]8397[/TD]
[TD].[/TD]
[TD].[/TD]
[TD].[/TD]
[TD].[/TD]
[TD].[/TD]
[TD].[/TD]
[TD].[/TD]
[TD].[/TD]
[TD].[/TD]
[TD].[/TD]
[TD].[/TD]
[TD]IS[/TD]
[/TR]
</tbody>[/TABLE]

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.