Sum with multiple row

[trduy1908]

Hi Everyone,

I have table with 4 column A, B, C, D:

Data sample:

text A
Pre-condition
	testB1	OK
	testB2	N/Y
	testB3	NG
text B
	testB1	OK
	testB2	OK
	testB3	OK
	testB4	OK
	testB5	OK
text C
	testB1	OK
	testB4	N/Y
	testB5	OK

I want to fill value on column D with condition column D : ((C3:C5="False")+(C3:C5="N/Y"))>0,"False","Pass") on row D1, D6, D12 (D is dynamic) (value on D row base on value on column B or column C).
Please help me or suggest keyword to search.

Thanks a lot.

 

[awoohaw]

I think there are some ambiguous parts in your set up.
Can you explain what you're testing with the C3:C5="false" portion? I don't see any places in column C where the word "false" seems to be a value that would appear.

Also, what is the signifance of the "Pre-condition" row in Row 2?

As you can see below, breaking out your formula into to smaller calculations you get a mostly false results. Are you requiring all test for each text to be OK or NG, or at least one test in the text ranges?

Book1
	A	B	C	D	E	F	G	H	I
1	text A			False
2	Pre-condition
3		testB1	OK		FALSE	=C3="False"		FALSE	=C3="N/Y"
4		testB2	N/Y		FALSE	=C4="False"		TRUE	=C4="N/Y"
5		testB3	NG		FALSE	=C5="False"		FALSE	=C5="N/Y"
6	text B			Pass
7		testB1	OK		FALSE	=C7="False"		FALSE	=C7="N/Y"
8		testB2	OK		FALSE	=C8="False"		FALSE	=C8="N/Y"
9		testB3	OK		FALSE	=C9="False"		FALSE	=C9="N/Y"
10		testB4	OK		FALSE	=C10="False"		FALSE	=C10="N/Y"
11		testB5	OK		FALSE	=C11="False"		FALSE	=C11="N/Y"
12	text C			False
13		testB1	OK		FALSE	=C13="False"		FALSE	=C13="N/Y"
14		testB4	N/Y		FALSE	=C14="False"		TRUE	=C14="N/Y"
15		testB5	OK		FALSE	=C15="False"		FALSE	=C15="N/Y"
16
Sheet6

Cell Formulas
Range	Formula
E3:E5,E13:E15,E7:E11	E3	=C3="False"
F3:F5,I13:I15,F13:F15,I7:I11,F7:F11,I3:I5	F3	=FORMULATEXT(E3)
H3:H5,H13:H15,H7:H11	H3	=C3="N/Y"

 

[trduy1908]

Thanks! Your response awoohaw.

So all value in column A, B, C, I will be input data by hand, example: C3: OK, C4: False, C5: N/Y (I have list value {OK, False, N/A, NG} to input on column C)

Also, what is the signifance of the "Pre-condition" row in Row 2? -> "Pre-condition" is only text, and it's inputted by hand (it's occasionally or not)

I want to value on D (detail D1, D6 or D12 base on value on column example: D1 (C3: C5), D6 (C7:C11), D12 (C14:C18))

If the number of lines increases (or decreases) so D1 (C3:C7), D6 change to D8 ( C9: C13) and D12 change to D14 (C16:C20) (The value of the cell in Column C can change and input by hand)

 

[awoohaw]

I am pretty darn sure there is an easier way to do this. Power Query is a great tool, and I initially thought I could do that with only that.
But, as it turns out, my brain may not be thinking correctly, and I had to bring a table in from power query to use to do the final calculations.

So, the power query formula is this:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> "Pre-condition")),
#"Filled Down" = Table.FillDown(#"Filtered Rows",{"Column1"}),
#"Filtered Rows1" = Table.SelectRows(#"Filled Down", each ([Column2] <> null))
in
#"Filtered Rows1"


And the worksheet is this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Column1	Column2	Column3		OK							Column1	Column2	Column3
2	text A				False							text A	testB1	OK
3	Pre-condition								DataValidationList			text A	testB2	N/Y
4		testB1	OK						False			text A	testB3	NG
5		testB2	N/Y						OK			text B	testB1	OK
6		testB3	NG						N/Y			text B	testB2	OK
7	text B				Pass				NG			text B	testB3	OK
8		testB1	OK									text B	testB4	OK
9		testB2	OK									text B	testB5	OK
10		testB3	OK									text C	testB1	OK
11		testB4	OK									text C	testB4	N/Y
12		testB5	OK									text C	testB5	OK
13	text C				False
14		testB1	OK
15		testB4	N/Y
16		testB5	OK
17
Sheet7

Cell Formulas
Range	Formula
E2,E13,E7	E2	=IF(COUNTIFS(Table1_1[Column1],A2,Table1_1[Column3],"<>OK")<1,"Pass","False")

Cells with Data Validation
Cell	Allow	Criteria
C4:C16	List	=$I$4:$I$7

 

[trduy1908]

Thanks! awoohaw
I will look into it and learn.