13 Replies Latest reply on Mar 31, 2011 2:01 PM by michael_alexander@me.com

# SUM IF

SUM IF

### Post

Hello, i want to SUM a field called PAID (in another Table) if a status is on payed

The other table is CUSTOMER.

How do i do that?

Thanks.

• ###### 1. Re: SUM IF

heyy michael,

first tables must be linked throw a joint

K|Z

• ###### 2. Re: SUM IF

So you have a customer layout and you need to see the total amount paid in the related table?

Assuming a relationship such as:

Customer::CustomerID = Payments::CustomerID

You can define Sum ( Payments::Paid ) in the customer table.

Note sure what you mean by this sentence though:  "if a status is on payed"

You may need to explain that in more detail here.

• ###### 3. Re: SUM IF

That's right!

What i mean by "if a status is on payed" is that the sum only sums the records where the "status is on payed".

CUSTOMER = Phil PAID = €5,00  STATUS = Payed

CUSTOMER = Michael PAID = €3,50 STATUS = Billed

CUSTOMER = Phil PAID = €3,50 STATUS = Billed

The 'SUM IF' must have the result €5,00.

Clear?

• ###### 4. Re: SUM IF

Define a calculation field, cIfPaid, in the payment table as:

If ( Status = "Paid" ; Paid )

Then, in customer, define the calculation as Sum ( PaymentTable::cIfPaid )

• ###### 5. Re: SUM IF

Thanks!

• ###### 6. Re: SUM IF

I want to add another date function....

The datefield is like PAID DATE <31-03-2011>.

I want only the records for 2011.

So If ( Status = "Paid" and Paid date is .......  ; Paid )

What is the date function?

• ###### 7. Re: SUM IF

Date ( 3 ; 31 ; 2011 )

or

GetasDate ( "31-03-2011" ) //assumes DDMMYYYY date settings rather than the US settings of MMDDYYYY

• ###### 8. Re: SUM IF

You can also calculate today's date using Get ( CurentDate ) but then make sure to use storage options to set the calculation to be Unstored.

• ###### 9. Re: SUM IF

Thanks.

More specific.

I want (only) all the records for 2011. Not 31-3 (that's an example.).

Clear?

• ###### 10. Re: SUM IF

Year(Datefield) = Year ( Get ( CurrentDate ) )

or Year ( DateFIeld ) = YourTable::GlobalYearfield

The second option allows you to set up a global field where you specify the year you want to see. (Useful when you are in the first quarter and want to go back and see stuff from the 4th quarter.)

There are also ways to set up relationships that limit the accessable records by a given date, month or year.

• ###### 11. Re: SUM IF

So the function become: If ( Status = "Paid" and Year(Paiddate) = "2011"  ; Paid )

• ###### 12. Re: SUM IF

That works until the year becomes 2012. That's where the other options that use a global field or Year ( Get (CurrentDate ) ) allow you to get the same results without having to redefine the field with each new year.

• ###### 13. Re: SUM IF

Ok, thanks!