Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello,
WE are trying to track how much work is contracted out. All numbers that start with 1 in STEP 1, 2, 3 belong to our company everything us else is contracted out. We would like to count everything that does not start with 1 so it can be shown on a card.
STATUS | TRIP | STEP 1 | STEP 2 | STEP 3 | TOTAL ? |
Complete | P26332 | 126 | 126 | GO928 | |
Complete | P26321 | 126 | 126 | TRACT | |
complete | D84470 | 101 | 101 | DFG | |
Complete | D84416 | 114 | 107 | SDF | |
Complete | D84457 | 116 | 108 | 132 | |
complete | D84523 | 121 | 123 | 234 | |
complete | D84557 | 109 | 109 | 123 | |
complete | D84405 | 120 | TRACT | 874 | |
complete | D84505 | GO123 | 102 | ||
complete | P26345 | 110 | 110 | ||
complete | D84534 | 108 | 108 | ||
complete | D84533 | 118 | 118 | 234 | |
Complete | P26344 | 107 | 107 | 534 | |
Complete | P26318 | 101 | 101 | 123 | |
complete | P26343 | 429 | 429 | 133 | |
complete | P26313 | 124 | 124 | ||
complete | P26340 | 114 | 114 | 456 | |
complete | P26341 | 108 | 108 | 123 |
Solved! Go to Solution.
Then you should add that condition to the formula, it would look something like:
TOTAL = IF(ISBLANK(STEP1)=TRUE,0,IF(LEFT(STEP1,1)="1",0,1))+IF(ISBLANK(STEP2)=TRUE,0,IF(LEFT(STEP2,1)="1",0,1))+IF(ISBLANK(STEP3)=TRUE,0,IF(LEFT(STEP3,1)="1",0,1))
Let me know if it works
I'm not sure if I follow, but I think you can calculate that as a column by means of a formula like: TOTAL = IF(LEFT(STEP1,1)="1",0,1)+IF(LEFT(STEP2,1)="1",0,1)+IF(LEFT(STEP3,1)="1",0,1)
This will give you the sum in the total field of subcontracted steps per trip
Hey @alexvc this works well but I forgot to ask can we have blanks not be included in the sum but rather be treated as our drivers who start with 1. I tried to change it myself but failed.
Then you should add that condition to the formula, it would look something like:
TOTAL = IF(ISBLANK(STEP1)=TRUE,0,IF(LEFT(STEP1,1)="1",0,1))+IF(ISBLANK(STEP2)=TRUE,0,IF(LEFT(STEP2,1)="1",0,1))+IF(ISBLANK(STEP3)=TRUE,0,IF(LEFT(STEP3,1)="1",0,1))
Let me know if it works
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
85 | |
84 | |
73 | |
49 |
User | Count |
---|---|
142 | |
135 | |
110 | |
68 | |
55 |