435,269 Members | 1,506 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,269 IT Pros & Developers. It's quick & easy.

# Count fields greater than zero using expressionbuilder

 P: 9 Hi all, Please help with the above issue as I am absolutely desperate. There should be a simple solution. I am working on timesheets where the fields Saturday - Sunday have different jobnumbers. Example: Sat Sun Mon Tues Weds Thurs Fri Days 10 12 08 07 06 13 0 6 At the end of every row is a column called 'days'. I would like to count the days everyone has worked, but EXCLUDE the 0 (as this represents a day off). The expression I am using at the moment is: =(IsNull([Sat]))+(IsNull([Sun]))+(IsNull([Mon]))+(IsNull([Tues]))+(IsNull([Weds]))+(IsNull([Thurs]))+(IsNull([Fri]))+7 (as I couldn't get the 'isnull' to work). However, this returns 7 in the days 'field'. But it needs to be a 6 as I do not want to count cells that include 0. Please can someone help? Let me know if you need any more information. Cheers Jul 22 '14 #1

#### ✓ answered by twinnyfo

If your value is truly 0 (zero), then the field is not null. For whatever reason, you are adding 7 to the end of your expression, so unless any values are actually null, you will get 7, but any Null values will decrement by 1 (as "True = -1"). Do you want to count the days that are not 0 or sum the values of those days? To count days:

Expand|Select|Wrap|Line Numbers
1. =IIf([Sat]<>0,1,0) +
2.     IIf([Sun]<>0,1,0) +
3.     IIf([Mon]<>0,1,0) +
4.     IIf([Tues]<>0,1,0) +
5.     IIf([Weds]<>0,1,0) +
6.     IIf([Thurs]<>0,1,0) +
7.     IIf([Fri]<>0,1,0)
Hope this helps

4 Replies

 Expert Mod 2.5K+ P: 3,284 If your value is truly 0 (zero), then the field is not null. For whatever reason, you are adding 7 to the end of your expression, so unless any values are actually null, you will get 7, but any Null values will decrement by 1 (as "True = -1"). Do you want to count the days that are not 0 or sum the values of those days? To count days: Expand|Select|Wrap|Line Numbers =IIf([Sat]<>0,1,0) +      IIf([Sun]<>0,1,0) +      IIf([Mon]<>0,1,0) +      IIf([Tues]<>0,1,0) +      IIf([Weds]<>0,1,0) +      IIf([Thurs]<>0,1,0) +      IIf([Fri]<>0,1,0) Hope this helps Jul 22 '14 #2

 P: 9 twinnyfo, thank you so much for your prompt reply. This code works perfectly and is exactly what I needed. I wanted to count the days worked (=>0), excluding days off (=0). Massive massive thanks! Jul 23 '14 #3

 Expert Mod 2.5K+ P: 3,284 emperial, Glad I could be of assistance. hope to see you around here again! Jul 23 '14 #4

 100+ P: 163 I'm doing something similar, but its not working for me. Here is what I put: Expand|Select|Wrap|Line Numbers =Count(IIf([ChangeOverMinutes]<>0,1,0)) I only want a count of the records that are greater than zero. Never mind I have it figured out now. Oct 19 '17 #5