6 Replies Latest reply: Jan 18, 2017 4:07 AM by Wenjun Wu

# How to count number of not Null values in a row

SeptOctNovDecAverage
332(3+3+2+0)/3
2541(2+5+4+1)/4
34(3+4+0+0)/2

How to count number of values across 4 data columns, excluding null values?

Thank you.

• ###### Re: How to count number of not Null values in a row

RangeSum(IF(Len(Trim(Sept))<>0,1),IF(Len(Trim(Oct))<>0,1),IF(Len(Trim(Nov))<>0,1),IF(Len(Trim(Dec))<>0,1))

• ###### Re: How to count number of not Null values in a row

Hi,

With reference to manish kachhia

Try this

RangeSum(Sept,Oct,Nov,Dec)/RangeSum(IF(Len(Trim(Sept))<>0,1),IF(Len(Trim(Oct))<>0,1),IF(Len(Trim(Nov))<>0,1),IF(Len(Trim(Dec))<>0,1))

it works!!

--Surendra

• ###### Re: How to count number of not Null values in a row

Is there a way to do this dynamically so that in case new columns are added the expression need not be changed each time?

• ###### Re: How to count number of not Null values in a row

Hi Surendra and Manish,

Thank you for your reply.

It works.

Ya I have the same question with Anushree below.

When data months are added, we have to change the expression again, is there a way like count dynamically?

Thanks!

• ###### Re: How to count number of not Null values in a row

For starters you could use 2 range functions for your expression:

rangesum(Sept, Oct, Nov,Dec) / rangenumericccount(Sept, Oct, Nov,Dec)

This eliminates the LEN and Trim functions.

For the next part please refer to this post: Re: Create script expression from variable no of fields

• ###### Re: How to count number of not Null values in a row

Thanks! RangeNumericCount() function shorten by alot !