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]

You need to use the formula in post#7 & then use a custom cell format of your choice. It's a lot simpler than doing it in the formula & the numbers will remain as numbers rather than being text.

 

[bearcub]

Understood. I have played with it trying to change the Total payment column from Text to Number using the Format Cells --> Custom formatting option but the cells are unresponsive. When I hard the formula I can convert it from text to numeric but I can't see to do it while it is part of the array formula - unless there is something wrong with my application (spread sheets). First time this has happened to me.

 

[Fluff]

Are you using the formula from post#7? Because that leaves them all as numbers, rather than converting them to text & you can simply change the cell format

Fluff.xlsm
	A	B	C	D	E
1
2	Kent	Bramley and Stanningley	434885	423,865.00	E11000019
3		Hounslow Central	175927	513,889.00	E11000009
4		Brasted, Chevening and Sundridge	156270	550,874.00	E10000016
5			Total	1,488,628.00
Report

Cell Formulas
Range	Formula
B2:E5	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),VSTACK(a,CHOOSE({1,2,3,4},"","Total",SUM(INDEX(a,,3)),"")))
Dynamic array formulas.

 

[bearcub]

Ah, I see the issue. I inserted a ~# in part of the formula. Other than having to format Column C to text everything is good.

Thank you. I was playing the formula and didn't see that I had changed the formatting, sorry

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	7,500.00	EUR
3	GERMANY	Max Kleemann (6GXPEMSCY)	405000	7,500.00	EUR
4	GERMANY	Roy Schultheiss (LN8ZWI2KV)	405000	98,481.34	EUR
5	GERMANY	Sirko Schoeder (7KAXF29MO)	405000	7,500.00	EUR
6	GERMANY	Zoran Duvnjak (BORGUWVSV)	410000	2,550.45	EUR
7	GERMANY	Tatjana Wild (V02AD8W51)	430000	6,545.47	EUR
8			Total	130,077.26
Germany

Cell Formulas
Range	Formula
B2:E8	B2	=LET(a,CHOOSECOLS(FILTER('Comm_Sept 22 Intl Summary'!D4:O73,('Comm_Sept 22 Intl Summary'!D4:D73= A2)*('Comm_Sept 22 Intl Summary'!N4:N73<>0)),2,4,11,12),VSTACK(a,CHOOSE({1,2,3,4},"","Total",SUM(INDEX(a,,3)),"")))
Dynamic array formulas.

 

[Fluff]

Glad to help & thanks for the feedback.

 

[bearcub]

I am totally in awe of what you moderators do for us on the forum. Otherwise, I would be totally struggling doing my job because of the manual intervention I have to do at my company. Hats off to you, Peter, and the others (I only remember you to, sorry)

 

[bearcub]

I was trying to modify the formula to fit another file where the range is entirely different because it is a smaller file. Can I use this thread for help or should I create another questions?

I tried to modify the range and number of colmns but I am getting a #CALC! error. Am I referencing the wrong Total range in the source sheet?

This is what I am trying to modify: The range is b4:I73, filter range is C4:C73 & I only want to choose 4 columns instead of 5.

Compensation Summary Brazil - Oct 22 - September 22 Commission Payments.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Country	Person Name	Dept	Total	Cur
2	BRAZIL	#CALC!
3						Source
4						Range: Columns
5
6
7						Sheet	Comm_ 2022 Brazil Sept Summary
8
9
10						Formula Used in another file
11
12
13
14
15
16
17
18
19
20
21
22
Argentina

Cell Formulas
Range	Formula
B2	B2	=+LET(a,CHOOSECOLS(FILTER('Comm_ 2022 Sept Brazil Xactly'!B4:J73,('Comm_ 2022 Sept Brazil Xactly'!C4:C73= A2)*('Comm_ 2022 Sept Brazil Xactly'!I4:I73<>0)),1,3,4,8),VSTACK(a,CHOOSE({1,2,3,4},"","Total",SUM(INDEX(a,,3)),"")))

These aren't showing up in the upload for reference:

 

[Fluff]

The formula looks fine, the #Calc error suggests that there are no cells with "Brazil" in col C

 

[bearcub]

thank you, this is strange though because my source data in Column C does have Brazil per below.

 

[Fluff]

In that case check that there are no leading/trailing spaces in col C or in A2