Excluding non zero values using the filter function

bearcub

Well-known Member
Joined
May 18, 2005
Messages
701
Office Version
  1. 365
  2. 2013
  3. 2010
  4. 2007
Platform
  1. Windows
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 DeptTotal PaymentCur
GERMANY Marc Ackermann (ZHGKQG5VN) 4050007,500.00 EUR
GERMANY Max Kleemann (6GXPEMSCY) 4050007,500.00 EUR
GERMANY Roy Schultheiss (LN8ZWI2KV) 40500098,481.34 EUR
GERMANY Sirko Schoeder (7KAXF29MO) 4050007,500.00 EUR
GERMANY Emel Rehberg (BPV7QDR90) 4050000.00
GERMANY Zoran Duvnjak (BORGUWVSV) 4100002,550.45 EUR
GERMANY Tatjana Wild (V02AD8W51) 4300006,545.47 EUR
Total130,077.26
 
I checked and no leading or trailing zeros. The issue that was confusing me was the VStack function and Choose. Given the original formula, it does look correct. Could it be that my input range is correct since I am starting with the rep name instead of the country name as I did. I'll reformat this file so it emulates the original file and see what happens. The file itself might be corrupted because I have no doubt the formula is correct.
 
Upvote 0

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
I suspect that the sum range is wrong, but that shouldn't return a #Calc error.
 
Upvote 0
I did try adjusting the sum range to a smaller range but I still had the same issue. I will adjust the file to the dimensions of the file that works and go from there. Thank you for all your help.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top