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]

Well this doesn't really solve my problem. The formula you've stated above should first of all only consider "FALSE" but that's a minor change.

Secondly,your formula doesn't include the frequency of the values in column A. That's what I want and need!

The information in column B and C in my "real document" are fixed meaning I can state them with for example ="D" but I don't control the values in column A.

Column A? In post #1 there is NO data in column A!
The data sample you posted has data in columns C, G , L
Could you clarify?

Also, please, confirm if the values FALSE ib column L are text or logical values - to check put in an empty cell
=ISTEXT(L2)
tell us what the formula returns

M.

 

[Marcelo Branco]

No the correct answer is 1 because 14834568 is the only number in column A that only have false and D in all cases.

Oh, you do want FALSE in ALL cases. There is NO data in column A in your data sample.

M.

 

[Supermike1983]

Oh, you do want FALSE in ALL cases. There is NO data in column A in your data sample.

M.

Sorry all, Column A obviously doesn't exist. What I meant was the leftmost column, meaning column C

 

[Supermike1983]

Oh, you do want FALSE in ALL cases. There is NO data in column A in your data sample.

M.

The important thing is that I want the following: "the sum of unique values in Column C that is both only "D"and "FALSE". Nothing else.

 

[Marcelo Branco]

What about the values in column L (FALSE, FALSE, TRUE...)? Are they text values or logical values?
Check using
=ISTEXT(L2)

M.

 

[Marcelo Branco]

The important thing is that I want the following: "the sum of unique values in Column C that is both only "D"and "FALSE". Nothing else.

Ok i understood. Please, confirm the values in column L - text or logical?

M.

 

[Supermike1983]

Column A? In post #1 there is NO data in column A!


Also, please, confirm if the values FALSE ib column L are text or logical values - to check put in an empty cell
=ISTEXT(L2)
tell us what the formula returns

M.

When writing =ISTEXT(L2) the return is "FALSE"

 

[Marcelo Branco]

When writing =ISTEXT(L2) the return is "FALSE"

So they are logical values!

That said, considering your data sample in post #1 (data in columns C, G and L, rows 2 to 10) try this array formula

=SUM(IF(FREQUENCY(IF(Report!C2:C10<>"";IF(COUNTIF(Report!C2:C10;Report!C2:C10)=COUNTIFS(Report!C2:C10;Report!C2:C10;Report!G2:G10;"D";Report!L2:L10;FALSE);MATCH("~"&Report!C2:C10;Report!C2:C10&"";0)));ROW(Report!C2:C10)-ROW(Report!C2)+1);1))
Ctrl+Shift+Enter

M.

 

[Supermike1983]

So they are logical values!

That said, considering your data sample in post #1 (data in columns C, G and L, rows 2 to 10) try this array formula

=SUM(IF(FREQUENCY(IF(Report!C2:C10<>"";IF(COUNTIF(Report!C2:C10;Report!C2:C10)=COUNTIFS(Report!C2:C10;Report!C2:C10;Report!G2:G10;"D";Report!L2:L10;FALSE);MATCH("~"&Report!C2:C10;Report!C2:C10&"";0)));ROW(Report!C2:C10)-ROW(Report!C2)+1);1))
Ctrl+Shift+Enter

M.

The formula works! Thank you so much for the great help!

If you don't mind I now have a final problem related to the one you helped me solve. It's a extension of the previous problem really.

 

[Supermike1983]

If I in the first example add dates in a new column, Column M.

I want to add the condition to just count the frequency of the unique values in Column C if the newest date in Column M corresponds to "D" in Column G and "FALSE" in Column L.

[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Column C[/TD]
[TD]Column G[/TD]
[TD]Column L[/TD]
[TD]Column M[/TD]
[/TR]
[TR]
[TD]6548923[/TD]
[TD]A[/TD]
[TD]FALSE[/TD]
[TD]2015-12-01[/TD]
[/TR]
[TR]
[TD]6548923[/TD]
[TD]A[/TD]
[TD]FALSE[/TD]
[TD]2016-08-15[/TD]
[/TR]
[TR]
[TD]6548923[/TD]
[TD]B[/TD]
[TD]TRUE[/TD]
[TD]2016-09-12[/TD]
[/TR]
[TR]
[TD]6548923[/TD]
[TD]D[/TD]
[TD]FALSE[/TD]
[TD]2016-10-17[/TD]
[/TR]
[TR]
[TD]14834568[/TD]
[TD]D[/TD]
[TD]FALSE[/TD]
[TD]2016-08-09[/TD]
[/TR]
[TR]
[TD]14834568[/TD]
[TD]D[/TD]
[TD]FALSE[/TD]
[TD]2016-11-14[/TD]
[/TR]
[TR]
[TD]8746985[/TD]
[TD]A[/TD]
[TD]FALSE[/TD]
[TD]2016-10-01[/TD]
[/TR]
[TR]
[TD]8746985[/TD]
[TD]B[/TD]
[TD]TRUE[/TD]
[TD]2016-08-07[/TD]
[/TR]
[TR]
[TD]8746985[/TD]
[TD]D[/TD]
[TD]FALSE[/TD]
[TD]2015-06-11[/TD]
[/TR]
</tbody>[/TABLE]