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.

Column C	Column G	Column L
6548923	A	FALSE
6548923	A	FALSE
6548923	B	TRUE
6548923	D	FALSE
14834568	D	FALSE
14834568	D	FALSE
8746985	A	FALSE
8746985	B	TRUE
8746985	D	TRUE

<tbody>
</tbody>

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

	N​
1​	Newest Date?​
2​	No​
3​	No​
4​	No​
5​	Yes​
6​	No​
7​	Yes​
8​	Yes​
9​	No​
10​	No​

<tbody>
</tbody>

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

	N​
1​	Newest Date?​
2​	No​
3​	No​
4​	No​
5​	Yes​
6​	No​
7​	Yes​
8​	Yes​
9​	No​
10​	No​

<tbody>
</tbody>

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.