Index if

[AbrahamGluck]

I would like to have a filtered report on a new sheet that contains an index of values of column A only if column B or C is above 0, can someone help me please,
Thank you in Advance!

	Jun 2020	Jul 2020	Aug 2020	Sep 2020	Oct 2020
Bob's Burger Joint					28.49
Books by Bessie				55.00	75.00
Brosnahan Insurance Agency	2,241.23
Cal Telephone	74.36			56.50
Chin's Gas and Oil	417.85		54.55	62.01
Diego's Road Warrior Bodyshop	755.00
Ellis Equipment Rental	112.00	18.08
Hall Properties	900.00	103.55	18.08
Hicks Hardware			103.55	250.00	370.51
Lee Advertising			75.00		74.86
Mahoney Mugs					18.08
Norton Lumber and Building Materials					103.55
Pam Seitz					75.00
PG&E				86.44	114.09
Robertson & Associates	300.00	100.00	250.00		315.00
Squeaky Kleen Car Wash					59.97
Tania's Nursery			158.08	197.18	207.85
Tim Philip Masonry					666.00
Tony Rondonuwu					100.00
TOTAL	$ 4,800.44	$ 221.63	$ 659.26	$ 707.13	$ 2,208.40

 

[AhoyNC]

Maybe something like this.
If you have Excel 365 with the FILTER function then example 1.
If not then try the INDEX function in example 2.

Data: Sheet1

Book1
	A	B	C	D	E	F
1		6/1/2020	7/1/2020	8/1/2020	9/1/2020	10/1/2020
2	Bob's Burger Joint					28.49
3	Books by Bessie				55	75
4	Brosnahan Insurance Agency	2,241.23
5	Cal Telephone	74.36			56.5
6	Chin's Gas and Oil	417.85		54.55	62.01
7	Diego's Road Warrior Bodyshop	755
8	Ellis Equipment Rental	112	18.08
9	Hall Properties	900	103.55	18.08
10	Hicks Hardware			103.55	250	370.51
11	Lee Advertising			75		74.86
12	Mahoney Mugs					18.08
13	Norton Lumber and Building Materials					103.55
14	Pam Seitz					75
15	PG&E				86.44	114.09
16	Robertson & Associates	300	100	250		315
17	Squeaky Kleen Car Wash					59.97
18	Tania's Nursery			158.08	197.18	207.85
19	Tim Philip Masonry					666
20	Tony Rondonuwu					100
21	TOTAL	$4,800.44	$221.63	$659.26	$707.13	$2,208.40
Sheet1

Example 1 with FILTER

Book1
	A
1	With FILTER function
2	Brosnahan Insurance Agency
3	Cal Telephone
4	Chin's Gas and Oil
5	Diego's Road Warrior Bodyshop
6	Ellis Equipment Rental
7	Hall Properties
8	Robertson & Associates
Sheet3

Cell Formulas
Range	Formula
A2	A2	=FILTER(Sheet1!$A$2:$A$20,(Sheet1!$B$2:$B$20)+(Sheet1!$C$2:$C$20)<>0,"")

Example 2 with INDEX:

Book1
	A
1	With INDEX
2	Brosnahan Insurance Agency
3	Cal Telephone
4	Chin's Gas and Oil
5	Diego's Road Warrior Bodyshop
6	Ellis Equipment Rental
7	Hall Properties
8	Robertson & Associates
Sheet2

Cell Formulas
Range	Formula
A2:A8	A2	=IFERROR(INDEX(Sheet1!$A$2:$A$20,AGGREGATE(15,6,(ROW(Sheet1!$A$2:$A$20)-ROW(Sheet1!$A$2)+1)/((Sheet1!$B$2:$B$20)+(Sheet1!$C$2:$C$20)<>0),ROWS($A$2:A2))),"")

 

[sandy666]

another option

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivot = Table.Unpivot(Source, {"06/01/2020", "07/01/2020"}, "Attribute", "Value"),
    TSC = Table.SelectColumns(Unpivot,{"Index"}),
    Distinct = Table.Distinct(TSC)
in
    Distinct

Index
Brosnahan Insurance Agency
Cal Telephone
Chin's Gas and Oil
Diego's Road Warrior Bodyshop
Ellis Equipment Rental
Hall Properties
Robertson & Associates
TOTAL

 

[AbrahamGluck]

Maybe something like this.
If you have Excel 365 with the FILTER function then example 1.
If not then try the INDEX function in example 2.

Data: Sheet1

Book1
	A	B	C	D	E	F
1		6/1/2020	7/1/2020	8/1/2020	9/1/2020	10/1/2020
2	Bob's Burger Joint					28.49
3	Books by Bessie				55	75
4	Brosnahan Insurance Agency	2,241.23
5	Cal Telephone	74.36			56.5
6	Chin's Gas and Oil	417.85		54.55	62.01
7	Diego's Road Warrior Bodyshop	755
8	Ellis Equipment Rental	112	18.08
9	Hall Properties	900	103.55	18.08
10	Hicks Hardware			103.55	250	370.51
11	Lee Advertising			75		74.86
12	Mahoney Mugs					18.08
13	Norton Lumber and Building Materials					103.55
14	Pam Seitz					75
15	PG&E				86.44	114.09
16	Robertson & Associates	300	100	250		315
17	Squeaky Kleen Car Wash					59.97
18	Tania's Nursery			158.08	197.18	207.85
19	Tim Philip Masonry					666
20	Tony Rondonuwu					100
21	TOTAL	$4,800.44	$221.63	$659.26	$707.13	$2,208.40
Sheet1

Example 1 with FILTER

Book1
	A
1	With FILTER function
2	Brosnahan Insurance Agency
3	Cal Telephone
4	Chin's Gas and Oil
5	Diego's Road Warrior Bodyshop
6	Ellis Equipment Rental
7	Hall Properties
8	Robertson & Associates
Sheet3

Cell Formulas
Range	Formula
A2	A2	=FILTER(Sheet1!$A$2:$A$20,(Sheet1!$B$2:$B$20)+(Sheet1!$C$2:$C$20)<>0,"")

Example 2 with INDEX:

Book1
	A
1	With INDEX
2	Brosnahan Insurance Agency
3	Cal Telephone
4	Chin's Gas and Oil
5	Diego's Road Warrior Bodyshop
6	Ellis Equipment Rental
7	Hall Properties
8	Robertson & Associates
Sheet2

Cell Formulas
Range	Formula
A2:A8	A2	=IFERROR(INDEX(Sheet1!$A$2:$A$20,AGGREGATE(15,6,(ROW(Sheet1!$A$2:$A$20)-ROW(Sheet1!$A$2)+1)/((Sheet1!$B$2:$B$20)+(Sheet1!$C$2:$C$20)<>0),ROWS($A$2:A2))),"")

Thank so much, I am using google sheet so aggregate won't work, do you have a workaround for that?
I would also not want to use filter because I want to keep it in a separate sheet because the first sheet is connected to a third party app that will do changes to it and update the sheet constantly.

 

[AbrahamGluck]

I think I figured it out due to the help of the community thanks to all

=FILTER('sheet1'!A:M,'sheet1'!M:M<>0)

 

[Peter_SSs]

I am using google sheet

In future please do not ask Google Sheets questions in the Excel Questions forum ..

.. use this forum instead. I have moved this thread to that forum.