Adding 3 columns together to get a total to show on a card

[benjaminhoang]

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

[alexvc]

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

[benjaminhoang]

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.

[alexvc · Accepted 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