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!

 

[jorismoerings]

Hi,

Perhaps i'm simplifying your issue but it looks like you are looking for a count of somekind and (if that's true) why you wnat to do this by using the FREQUENCY function.

try this instead:

Excel 2016 (Windows) 64 bit
	A	B	C
1	6548923	A	FALSE
2	6548923	A	FALSE
3	6548923	B	TRUE
4	6548923	D	FALSE
5	14834568	D	FALSE
6	14834568	D	FALSE
7	8746985	A	FALSE
8	8746985	B	TRUE
9	8746985	D	TRUE
10
11		1
Sheet1

Cell Formulas
Range	Formula
B11	=SUMPRODUCT(--(B1:B9="D"),--(C1:C9=TRUE))

 

[ww4612]

you can use sumproduct formula
=sumproduct(--(r1c7:r9c7="d")*--(r1c12:r9c12=false))

 

[Marcelo Branco]

Your formula seems ok to me, but i think the correct answer is 2, not 1. Shouldn't 6548923 and 14834568 be counted?
If so, delete the double quotes around "FALSE" and try again (do not forget to confirm the formula with Ctrl+Shift+Enter, not just Enter).
Tell us the result.

M.

 

[Marcelo Branco]

Provided the values in column L are logical values TRUE or FALSE (not text values "TRUE" or "FALSE") this formula should work

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

M.

 

[Supermike1983]

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.

 

[Supermike1983]

Hi,

Perhaps i'm simplifying your issue but it looks like you are looking for a count of somekind and (if that's true) why you wnat to do this by using the FREQUENCY function.

try this instead:

Excel 2016 (Windows) 64 bit

	A	B	C
A
A
B
D
D
D
A
B
D

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]6548923[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]6548923[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]6548923[/TD]

[TD="align: right"]TRUE[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]6548923[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]14834568[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]14834568[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]8746985[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]8746985[/TD]

[TD="align: right"]TRUE[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]8746985[/TD]

[TD="align: right"]TRUE[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B11[/TH]
[TD="align: left"]=SUMPRODUCT(--(B1:B9="D"),--(C1:C9=TRUE))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[Supermike1983]

you can use sumproduct formula
=sumproduct(--(r1c7:r9c7="d")*--(r1c12:r9c12=false))

I don't follow your post, what does this mean with r1c7 - can't write that because Excel instantly complains....

 

[Supermike1983]

Provided the values in column L are logical values TRUE or FALSE (not text values "TRUE" or "FALSE") this formula should work

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

M.

Unfortunately the true or false are not logical values.... What to do then?

 

[Supermike1983]

Your formula seems ok to me, but i think the correct answer is 2, not 1. Shouldn't 6548923 and 14834568 be counted?
If so, delete the double quotes around "FALSE" and try again (do not forget to confirm the formula with Ctrl+Shift+Enter, not just Enter).
Tell us the result.

M.

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