New to formulas, need to write a Count If there is a value in a field. Trying to set up a numeric formula field.

  • 0
  • 1
  • Question
  • Updated 7 years ago
  • Answered
New to formulas, need to write a Count If formula field.
My Table records Monthly Results for Service Level Agreements.
I have set it up so results for the year are entered as one record for each software application. ie: Jan - Dec.
Not every month will have a result.
I need a formula field that counts the 12 month fields and only totals the fields that have a result, counting them as "1". So, if only Jan & March have a value in the field the result would be "2".

I know I am missing a piece on "if there is a valu", but can't figure out what it is. Here is the example of how I have it written:
Count([January Result],[February Result],[March Result],[April Result],[May Result],[June Result],[July Result],[August Result],[December Result],[November Result],[September Result],[October Result])

Photo of Nikki

Nikki

  • 284 Points 250 badge 2x thumb

Posted 7 years ago

  • 0
  • 1
Photo of keirab

keirab

  • 20 Points
The information you are missing is that there is a checkbox for:
"Treat blank values as "0" in calculations"
on the Field Properties for numeric fields that defaults to being checked.
Since the "Count" function will count all "non-null" values, it is seeing the empty result fields as zero, so they are counted because they are not considered "null".
So, you can either:
(a) uncheck the "Treat blank values as "0" in calculations" checkbox on each of your 12 results fields and leave the formula as is.
OR
(b) leave the boxes checked and change the formula to:
Sum(
If([January Result]>0,1,0),
If([February Result]>0,1,0),
<you can fill in with the same for each month>
)
Which method you use will depend on the other situations where you use the Results fields and how you want them to behave for zero vs. null.
Hope that helps,
Keira
Photo of Nikki

Nikki

  • 284 Points 250 badge 2x thumb
Thank you.  Yes!

This conversation is no longer open for comments or replies.