7 Replies Latest reply on Dec 22, 2016 12:41 PM by Susan Hildebrant

    Struggling with Custom SubTotal using Min()=Max() expression

    Susan Hildebrant

      I am a novice user and have been successful creating custom subtotals previously, but I am stumped on how to modify this custom subtotal to get the desired result. The calculation works perfectly if there is more than one value in the column. However if the column contains only one value, the Total Billable 2 calculation does not execute properly. Here are the calculations:

       

      Total Flag = If MIN([Project/Opportunity])=MAX([Project/Opportunity]) then 0 else 1 end

       

      Billable Hours = IF [Contract Status]<>0 THEN IFNULL([Hours],0)  end

       

      Total Billable 2 = IF([Total Flag])=0  then MAX([Hours]) else SUM([Billable Hours]) END

       

      Here is an example of the result:

      Apr 14 correctly calculates nothing since the Contract Status = 0%. April 15 correctly calculates 2 because Contract Status = 100%. June 22 incorrectly calculates 8 even though Contract Status is 0%. In this case, 8 is the only value in the column. Any ideas on how to fix? Adding zeroes everywhere is not an option.

       

      Thanks in advance for any ideas.

       

      Workbook attached.