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]

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.

Column C	Column G	Column L	Column M
6548923	A	FALSE	2015-12-01
6548923	A	FALSE	2016-08-15
6548923	B	TRUE	2016-09-12
6548923	D	FALSE	2016-10-17
14834568	D	FALSE	2016-08-09
14834568	D	FALSE	2016-11-14
8746985	A	FALSE	2016-10-01
8746985	B	TRUE	2016-08-07
8746985	D	FALSE	2015-06-11

<tbody>
</tbody>