I need to calculate the total quantity of the field {Quantity} based on different names stored in a field called {Substitute}.

 

I have tried:

 

Sum ({Quantity},(where {Substitute}="Tube")

 

This does not work.  I get this error message:

 

"Running Total\Summary Field could not be created"

 

Could someone please send me the correct syntax to use for this formula?

 

Thank you,

Richard Scott

 

I have continued to tinker with the formula and the following formula comes close but it sums the total for all items:

 

If {ProductNumber.ProductNumber}='100049'

   then sum({LineItem.Quantity}) else 0

The problem with the above formula is that it SUMS all the Quantity for all items but I only want it to SUM the Quantity if the roductNumber=100049.

 

I think the formula should be like (doesn't work):

 

Sum({LineItem.Quantity}) where {ProductNumber.ProductNumber}='100049'

 

But I get the error message " The remaining text does not appear to be part of the formula" right before "where".

 

Does anyone know the syntax for a conditional SUM where it only SUMs the items the condition is based on?

 

Thanks,

Richard Scott

 

 

That works!  Thank you so much for the direction.   I was stuck and had the pressure of needing this report complete by Thursday morning.

 

I appreciate everyone's input in helping me solve this issue.

 

Thank you,

Richard Scott