Paradigmgaming911
New Member
- Joined
- Aug 8, 2023
- Messages
- 9
- Office Version
- 2016
- Platform
- Windows
Hello and thank you in advance.
I am trying to automate some totals in a work sales log.
In the example spreadsheet (which is really just a copy of the original worksheet), I am trying to use a foruma to calculate/count bonuses for sales professionals.
On the worksheet, the sales professionals ('Sales Person' column P) get a $20 bonus for each time that column J is 'YES', each time colum K is "F&I", and each time columns R, S, T and U are "Y" (column Q being a "Y" is onlhy a $10 bonus)
The formula that I am trying to find for AF14 for example, is anytime 'Marko' is listed in column P, it uses =countifs(J3:J200, "YES", K3:K200, "F&I", R3:R200, "Y", S3:S200, "Y", T3:T200, "Y", U3:U200, "Y") ... where each positive occurance counts '1'.
Marko is listed 4 times in colum P as the Sales Person ... so I'd like AF14 to be the results of the countifs for each of the rows where he is listed. And, of course, each of the lines he would be listed in the future.
So AF14 would need to return a value of 5 (zero results from the countifs in row 16, 1 result in row 18 for "F&I", 2 results in row 20 for "YES" and "F&I" and 2 results in row 25 for "YES" and "F&I")
I cannot get Mini-Sheet to work because Capture Range inside XL2BB is not available. I've listed a link to the spreadsheet
docs.google.com
EDIT:
The formula in question in the OP is based on the example log listed and the tab AUGUST
I am trying to automate some totals in a work sales log.
In the example spreadsheet (which is really just a copy of the original worksheet), I am trying to use a foruma to calculate/count bonuses for sales professionals.
On the worksheet, the sales professionals ('Sales Person' column P) get a $20 bonus for each time that column J is 'YES', each time colum K is "F&I", and each time columns R, S, T and U are "Y" (column Q being a "Y" is onlhy a $10 bonus)
The formula that I am trying to find for AF14 for example, is anytime 'Marko' is listed in column P, it uses =countifs(J3:J200, "YES", K3:K200, "F&I", R3:R200, "Y", S3:S200, "Y", T3:T200, "Y", U3:U200, "Y") ... where each positive occurance counts '1'.
Marko is listed 4 times in colum P as the Sales Person ... so I'd like AF14 to be the results of the countifs for each of the rows where he is listed. And, of course, each of the lines he would be listed in the future.
So AF14 would need to return a value of 5 (zero results from the countifs in row 16, 1 result in row 18 for "F&I", 2 results in row 20 for "YES" and "F&I" and 2 results in row 25 for "YES" and "F&I")
I cannot get Mini-Sheet to work because Capture Range inside XL2BB is not available. I've listed a link to the spreadsheet
example eLog
JANUARY JANUARY 2023, tracking ,128,25,26, $ 366,084.93 , $ 196,554.49 , $ 155,450.25 , $ 352,004.74 DATE,Deal #, Trade Info , ACV ,Cust Name,AGE,Vehicle Type,F&I,Stock #, (FE) GROSS , (BE) GROSS , TOTAL GROSS ,Sales Pro,(F)ADDS,CPOV,MGR. 1,01/02/23,280514, NT ,NT,GARCIA,17,2012 NISSAN FRONTIE...
EDIT:
The formula in question in the OP is based on the example log listed and the tab AUGUST
Last edited by a moderator:
