IF Formula for measuring how many fields are valid per day

[Greyson]

I’m tracking some healthy practices every day:

Exercise = Multiple Select (Running, Pushups, Squats)
Mediation = Duration
Healthy Food = Checkbox

And some negative practices:
Alcohol = Number

If Exercise is not empty, I want the Formula to return +1
If Meditation is greater than 0:10, I want the Formula to return +1
If Healthy Food is checked, I want the Formula to return +1

If 2 of these are True, I want to return 2
If 3 of these are True, I want to return 3

If alcohol is greater than 1, I want to subtract 1. So if all 3 positive behaviors were valid it would be 3 - 1 = 2

Then I will create a color filter and apply:

0 or -1 = red
1 = orange
2 = yellow
3 = green

I know this is a complex request, I appreciate any help you may be able to provide.

Thank you in advance

[JonathanBowen · Accepted solution]

Hi @Greyson - you can do it like this:

IF(Exercise, 1, 0) + IF(Meditation/60 > 10, 1, 0) + IF({Healthy Food}, 1, 0) + IF(Alcohol > 1, -1, 0)

Basically, counting each column as 1 if a value exists, except for Alcohol which is -1 if greater than one, then summing each value.

There’s a bit of weird wrangling you need to do with the duration field - divide it by 60 to get an integer value, then compare to this (in your case 10, rather than 0:10)

JB

[Greyson]

@JonathanBowen this worked perfectly, thank you for your help.