4 Replies Latest reply: Jul 1, 2013 4:16 AM by Abey Chandy

# Counting zeros in a range

Hi,

Please see the above excel data. I am struggling with calculation for the "Count of Zeros" column above. It has to calcualate the number of zeros for the months passed for the financial year (Apr, May and Jun). Acitivity is a column based on the "count of zeros" column value.

The values for these months are calcualted values (expressions) and not directly available in my data set.

In excel we have something like - COUNTIF(N6:X6,0), that will give you the count of 0s between N6 and X6.

I checked the different range functions available (Rangecount, rangenumericcount), but none seems to be suitable for this situation. Was wondering if any chart inter record function would do the job.

Would be of great help if you could suggest something on this direction.

Thanks,

Abey

• ###### Re: Counting zeros in a range

Count(If(WildMatch(Field_Name,'0'),Field_Name))

• ###### Re: Counting zeros in a range

Thanks Sivaraj.

But this does not work because - my value is based on a calcualate value for "Field_Name".

I will explain a bit more - the values under 'Apr', 'May', 'Jun' are calcuated as sum({<month='Apr',.,.,.>}Field_Name), sum({<month='May',.,.,.>}Field_Name) and so on.

So my field needs to count the zeros across these different expressions.

Thanks,

Abey

• ###### Re: Counting zeros in a range

For--     sum({<month='Apr',.,.,.>}Field_Name)

write--

if(sum({<month='Apr',.,.,.>}Field_Name)  = 0 , count(sum({<month='Apr',.,.,.>}Field_Name)))

or also write this code

count(if(column1=0,column1))

*********************************

similarly write code for other months

• ###### Re: Counting zeros in a range

Hi Vishwaranjan,

Thanks a lot.

It worked...

count(if(column1=0,column1)) somehow was not giving me the count properly.

Then i used this:

If(column(4)=0,1,0) + If(column(5)=0,1,0) + If(column(6)=0,1,0)

Thanks,

Abey