Need some formula help with IF function

We have a board created where we keep track of credits for clients. They can either purchase credits for their account on demand or by subscription each month. As they complete projects each week/month workers use credits instead of hourly. We have the basic board created with tracking credits, but we are having problems getting a proper formula to work … hoping someone in here can help :slight_smile: Here is a run down so far of everything we have.

  1. We have a TOTAL CREDITS column created that will be the formula.
  2. We have columns for credits that have been applied to a clients account.
  3. At the end of the columns we have 2 columns that are mirrored from individual client boards for their projects.

Our current formula is the following:
MINUS(SUM({ACES 100 Credits},{50 Bonus Credits (purchase or transfer by 8/29/2021)},{Converted ACES DFY Credits},{TYGP - 2 Subscription Credits per Month},{30 Bonus Credits for TYGP (purchase by 9/14/2021)},{On-Demand Purchased Credits},{Subscription Purchased Credits})IF())

What we need to have in the formula is to ADD 7 columns together then MINUS the Used credits columns for a total of remaining credits available.

One thing to mention and very important is that many of these clients will have credits purchased but nothing will be used - so the row will be blank. Other clients will have used credits and their rows will have a number indicating how many credits were used.

Attaching 2 images below to show the setup … any help would be greatly appreciated :slight_smile:


@michellemedd,

This should do it:

{ACES 100 Credits} + {50 Bonus Credits (purchase or transfer by 8/29/2021)} + {Converted ACES DFY Credits} + {TYGP - 2 Subscription Credits per Month} + {30 Bonus Credits for TYGP (purchase by 9/14/2021)} + {On-Demand Purchased Credits} + {Subscription Purchased Credits} - {Used Credits}

Jim

Thanks @JCorrell for this - unfortunately I tried putting it in and still gives me an error (illegal formula). The last column (used credits) are all blank right now with no number or anything. It will only populate once credits have actually started being used. Thinking it would have an IF formula because even if the field is “blank” it still needs to total the TOTAL Credits column for everyone.

@michellemedd ,

If Used Credits is a number column, something else is wrong. If it is a formula column, just put a 0 (zero) in it for now. There should no need for an IF.

Its neither of those - its a mirrored column from boards we create for clients who have projects in progress. Its a column that will be linked to their individual project board in their workspace. So not all clients will have an answer for the mirrored column if they don’t have a project board. Does that make sense?

@michellemedd,

Yes. But what type of column is it on the projects board?

It should be pulling from a “formula” column as it has to sum the credits used based on time tracking per worker.

I think I ran into an issue that won’t allow me to actually see the proper setup of things … One of the main boards is hidden in private from me and this connects to the project board for credits per worker. Which is causing an error on a few different boards its connected to. I will have to contact someone in my company to change some permissions. Do you know if boards are private / not shared, will that affect mirrored columns?

@michellemedd,

Yes, I do know… have been bitten by that one myself.

Absolutely, when a user does not having access to anything “downstream”, the system will either return an error or it will look as though the referenced columns/boards don’t even exist. Even columns that you technically have access to can disappear like they were deleted. If something downstream changes.

1 Like

Thank you so much! That helps me out a lot. Will have to figure out a resolution to that before we continue with the above formula :slight_smile:

Ok I have fixed my issue inside Monday … I have it working for one but the rest still show as errors in the total column and the Used Credits column is blank except for the one project I have connected to one client… Any ideas?


Hi @michellemedd :wave:

I rebuilt your board a bit using your column names and if all of these columns being added together are numbers columns while the mirror column of “Used Credits” is a umbers column on the other board, then this formula worked for me without giving an error:


MINUS(SUM({ACES 100 Credits},{50 Bonus Credits (purchase or transfer by 8/29/2021)},{Converted ACES DFY Credits},{TYGP - 2 Subscription Credits per Month},{30 Bonus Credits for TYGP (purchase by 9/14/2021)},{On-Demand Purchased Credits},{Subscription Purchased Credits}),{Used Credits})

Give that a try and let me know if it works for you!

Cheers,
Jenna