Frequency problem desperate for solution!

[Supermike1983]

I have a frequency problem I don't seem to find a solution for. Below you find an extract of just the columns I want to compare.

I want to write a frequency condition saying: "How many values in Column C does only include D values in Column G and FALSE in Column L?".

In this small example below the correct answer is 1 time.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column C[/TD]
[TD]Column G[/TD]
[TD]Column L[/TD]
[/TR]
[TR]
[TD]6548923[/TD]
[TD]A[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]6548923[/TD]
[TD]A[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]6548923[/TD]
[TD]B[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]6548923[/TD]
[TD]D[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]14834568[/TD]
[TD]D[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]14834568[/TD]
[TD]D[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]8746985[/TD]
[TD]A[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]8746985[/TD]
[TD]B[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]8746985[/TD]
[TD]D[/TD]
[TD]TRUE[/TD]
[/TR]
</tbody>[/TABLE]

The closest I get is this array formula:

{=SUM(IF(FREQUENCY(IF($C$2:Report!$C$10<>"";IF($L$2:$L$10="FALSE";IF($G$2:$G$10="D";MATCH("~"&$C$2:$C$10;$C$2:$C$10&"";0))));ROW($C$2:$C$10)-ROW($C$2)+1);1))}

Desperately need help!

 

[Marcelo Branco]

I suggest a helper column, say column N. Something like this


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

N​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Newest Date?​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

No​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

No​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

No​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Yes​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

No​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Yes​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

Yes​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

No​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

No​

[/TD]
[/TR]
</tbody>[/TABLE]


Array formula in N2 copied down
=IF(M2=MAX(IF($C$2:$C$10=C2,$M$2:$M$10)),"Yes","No")
Ctrl+Shift+Enter

Then, to count the unique values, try this array formula
=SUM(IF(FREQUENCY(IF(C2:C10<>"",IF(G2:G10="D",IF(L2:L10=FALSE,IF(N2:N10="Yes",MATCH(C2:C10,C2:C10,0))))),ROW(C2:C10)-ROW(C2)+1),1))
Ctrl+Shift+Enter

M.

 

[Marcelo Branco]

oops...
It seems that your Excel uses ; (semi-colon) as argument separator. So on each formula above replace , by ;

M.

 

[Supermike1983]

I suggest a helper column, say column N. Something like this


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

N​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Newest Date?​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

No​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

No​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

No​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Yes​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

No​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Yes​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

Yes​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

No​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

No​

[/TD]
[/TR]
</tbody>[/TABLE]


Array formula in N2 copied down
=IF(M2=MAX(IF($C$2:$C$10=C2,$M$2:$M$10)),"Yes","No")
Ctrl+Shift+Enter

Then, to count the unique values, try this array formula
=SUM(IF(FREQUENCY(IF(C2:C10<>"",IF(G2:G10="D",IF(L2:L10=FALSE,IF(N2:N10="Yes",MATCH(C2:C10,C2:C10,0))))),ROW(C2:C10)-ROW(C2)+1),1))
Ctrl+Shift+Enter

M.

In my "real" business case I can't predict the values in Column C and the dates in Column M. Do I in this case with the formula you provided me with need to sort for example the column C to get this right or is it applicable even if the numbers in Column C is totally random? The randomness is my main problem!

 

[Marcelo Branco]

What exactly do you mean by

In my "real" business case I can't predict the values in Column C and the dates in Column M.

I'm confused..
Well, stating the obvious ... the formula works if there are data in columns C, G, L and M
Could you, please, clarify

M.

 

[Supermike1983]

What exactly do you mean by


I'm confused..
Well, stating the obvious ... the formula works if there are data in columns C, G, L and M
Could you, please, clarify

M.

I was just thinking of the formulas in any way were specific regarding the example I stated. In my real document I can't predict the values etc.

But if it only is as you say, that the formulas are not value-specific more than that there needs to be a values in the column it's all good!

Thank you once again Marcelo, you've been more than helpful to me!

 

[Marcelo Branco]

You are welcome.

M.