FILTER Formula ignoring initial data after columns being filled

[Lacan]

Hello Guys,

Very simple have Table 1 with Clients that want to Filter according with selection "S" in Table 2.

But then in Table 2 have to filled with sales in the previous months and registered accordingly.

So the goal is that the Clients in Table 2 who have total sum >0 avoid being remove of Table 2 even if in Table 1 after in the future decided to remove the "S" from the selection.

To Resume in Table 2 want to keep all the clients who have in sales the total amount >0 even if in the future I decided to remove the "S" from selection in Table 1.

Is it possible with out macros?

Thank you very much guys.

Novo Folha de Cálculo do Microsoft Excel.xlsx
	B	C	D	E	F	G	H	I	J	K
2	TABLE 1					TABLE 2
3	NAME	POTENCIAL	SELECTION			NAME	OCT	NOV	DEC	TOTAL
4	JOHN	A	S			JOHN	5	5	5	15
5	PETER	C	S			PETER				0
6	ANDREA	B				OLGA	10	5	6	21
7	OLGA	B	S			FAISA				0
8	GABY	C				ERBA	20		2	22
9	FAISA	A	S			PURA				0
10	ERBA	C	S			JAINE	40		20	60
11	PURA	A	S			LYON				0
12	EURO	B				JASY				0
13	YGOR	B				OSCAR	5			5
14	JAINE	C	S			JACQUELINE				0
15	LYON	A	S			ANTERD	9			9
16	JASY	C	S			LEVIS				0
17	JASUS	C				C17	50			50
18	OSCAR	A	S							0
19	FILIPE	A								0
20	JACQUELINE	B	S							0
21	ANTERD	C	S							0
22	SOVIET	A								0
23	LEVIS	C	S							0
24	C17	B	S							0
25	FLY	A								0
Folha6

Cell Formulas
Range	Formula
G4:G17	G4	=FILTER(FILTER(B4:D25,D4:D25="S"),{1,0,0})
K4:K25	K4	=SUM(H4:J4)
Dynamic array formulas.

 

[jasonb75]

Is it possible with out macros?

In simple terms, no. A formula can only show results based on what is there now, not what was there before. Once you remove the 'S' it no longer meets the criteria for the formula so is subsequently excluded from the results.

Also, you don't need 2 filter functions to do what you're doing, 1 will suffice if written correctly.

=FILTER(B4:B25,D4:D25="S")

 

[Lacan]

In simple terms, no. A formula can only show results based on what is there now, not what was there before. Once you remove the 'S' it no longer meets the criteria for the formula so is subsequently excluded from the results.

Also, you don't need 2 filter functions to do what you're doing, 1 will suffice if written correctly.

=FILTER(B4:B25,D4:D25="S")

Thank you very much for the help.

In order to achieve that goal what is your opinion to get there?

Thanks again.

 

[jasonb75]

In order to achieve that goal what is your opinion to get there?

Realistically, don't delete the 'S' from the source table.

There are a few options with vba but I think that they are all going to be error prone with the way that you want to do things.

 

[Lacan]

Realistically, don't delete the 'S' from the source table.

There are a few options with vba but I think that they are all going to be error prone with the way that you want to do things.

Thanks a lot my friend.

Im gonna think how can reach the goal keeping the "S".

Best regards. ?????