Counting zeros in a range

[abeyphilip]

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

[sivarajs]

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

[abeyphilip]

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

[#4 · Accepted solution]

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

[abeyphilip]

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