Excluding non zero values using the filter function

[bearcub]

I need to create a separate sheet for payroll by country based off of a payroll sheet. I am using the Filter function to this.
FILTER(FILTER('Comm_Sept 22 Intl Summary'!D4:N73,'Comm_Sept 22 Intl Summary'!D4:D73= A2),{0,1,1,0,0,0,0,0,0,1,1})

1) Is there a more efficient way to write this formula? In my criteria range, I only want the 2,3,9 & 10th columns.
2) How do I exclude reps who don't have commissions. Right now, it will pull everyone on the summary sheet that is based upon the country criteria
3) Is there a way to create a dynamic sum range based upon what is spilled from the list. The range will vary from month to month and I will like to create a total at the end of the list dynamically.

Here is an example of what I have presently based upon my formula. The Total was manually added, is there a way to make this dynamic based upon current employees being paid

Country	Name	Dept	Total Payment	Cur
GERMANY	Marc Ackermann (ZHGKQG5VN)	405000	7,500.00	EUR
GERMANY	Max Kleemann (6GXPEMSCY)	405000	7,500.00	EUR
GERMANY	Roy Schultheiss (LN8ZWI2KV)	405000	98,481.34	EUR
GERMANY	Sirko Schoeder (7KAXF29MO)	405000	7,500.00	EUR
GERMANY	Emel Rehberg (BPV7QDR90)	405000	0.00
GERMANY	Zoran Duvnjak (BORGUWVSV)	410000	2,550.45	EUR
GERMANY	Tatjana Wild (V02AD8W51)	430000	6,545.47	EUR
		Total	130,077.26

 

[Fluff]

Do you have functions such as Expand & ChooseCols yet?

 

[bearcub]

Yes, I work for a company that is running 365

 

[bearcub]

I seem to have most of the new array functions

 

[Fluff]

Ok, how about

=LET(a,CHOOSECOLS(FILTER('Comm_Sept 22 Intl Summary'!D4:N73,('Comm_Sept 22 Intl Summary'!D4:D73= A2)*('Comm_Sept 22 Intl Summary'!M4:M73<>0)),2,3,10,11),SUBSTITUTE(VSTACK(a,{"","Total","~#",""}),"~#",SUM(INDEX(a,,3))))

 

[bearcub]

Wow, really cool. What I noticed is that I can't format the currency column. Excel is formatting everything as text. How do I get around that? How could I add the Text function to column 10 to format it as currency?

 

[Fluff]

Ok, how about

=LET(a,CHOOSECOLS(FILTER('Comm_Sept 22 Intl Summary'!D4:N73,('Comm_Sept 22 Intl Summary'!D4:D73= A2)*('Comm_Sept 22 Intl Summary'!M4:M73<>0)),2,3,10,11),VSTACK(a,CHOOSE({1,2,3,4},"","Total",SUM(INDEX(a,,3)),"")))

 

[bearcub]

This is what I presently see when I use the formula. I would like to have column D ("Total Payment") formatted to number format if possible:

Country	Name	Dept	Total Payment	Cur
GERMANY	Marc Ackermann (ZHGKQG5VN)	405000	7500	EUR
GERMANY	Max Kleemann (6GXPEMSCY)	405000	7500	EUR
GERMANY	Roy Schultheiss (LN8ZWI2KV)	405000	98481.3431	EUR
GERMANY	Sirko Schoeder (7KAXF29MO)	405000	7500	EUR
GERMANY	Zoran Duvnjak (BORGUWVSV)	410000	2550.44516	EUR
GERMANY	Tatjana Wild (V02AD8W51)	430000	6545.47	EUR
		Total	130077.25826

 

[Fluff]

In that case just format the column to whatever you need.

 

[bearcub]

I've tried to format Column D but am able to do so. I was able to format the total value but am trying to figure out how to format the individual payees.

This the formula I'm presently using:

LET(a,CHOOSECOLS(FILTER('Comm_Sept 22 Intl Summary'!D4:N73,('Comm_Sept 22 Intl Summary'!D4:D73=A2)*('Comm_Sept 22 Intl Summary'!M4:M73<>0)),2,3,10,11),SUBSTITUTE(VSTACK(a,{"","Total","~#",""}),"~#",TEXT(SUM(INDEX(a,,3)),"###,###.00")))

Compensation Summary Int'l Oct 22 - September 22 Commission Payments.xlsb
	A	B	C	D	E
1	Country	Name	Dept	Total Payment	Cur
2	GERMANY	Marc Ackermann (ZHGKQG5VN)	405000	7500	EUR
3	GERMANY	Max Kleemann (6GXPEMSCY)	405000	7500	EUR
4	GERMANY	Roy Schultheiss (LN8ZWI2KV)	405000	98481.3431	EUR
5	GERMANY	Sirko Schoeder (7KAXF29MO)	405000	7500	EUR
6	GERMANY	Zoran Duvnjak (BORGUWVSV)	410000	2550.44516	EUR
7	GERMANY	Tatjana Wild (V02AD8W51)	430000	6545.47	EUR
8			Total	130,077.26
9
Germany

Cell Formulas
Range	Formula
B2:E8	B2	=LET(a,CHOOSECOLS(FILTER('Comm_Sept 22 Intl Summary'!D4:N73,('Comm_Sept 22 Intl Summary'!D4:D73=A2)*('Comm_Sept 22 Intl Summary'!M4:M73<>0)),2,3,10,11),SUBSTITUTE(VSTACK(a,{"","Total","~#",""}),"~#",TEXT(SUM(INDEX(a,,3)),"###,###.00")))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E7:E8	Expression	=$K7 = "Draw"	text	NO
B6	Cell Value	duplicates	text	NO
E2:E3	Expression	=$K2 = "Draw"	text	NO
E4:E6,D2:D8	Expression	=$K2 = "Draw"	text	NO
D2:D8	Expression	=$B2 = "T"	text	NO
B4:B5	Cell Value	duplicates	text	NO
B6:B8,B2:B3	Cell Value	duplicates	text	NO