0

Formula for finding sum based on a condition

Hello,

Here is what I have:

1 - A number field where I put in a value

2 - a choice field where I have 3 options

3 - a formula field (in a linked table) where I would like to calculate the sum  of the numbers filed only if  choice 1 and 2 are selected, but leave out the value if choice 3. I spent hours trying to solve this and I cant for some reason get it to work. 

6 replies

null
    • Sean
    • 4 yrs ago
    • Reported - view

    Is the formula field in the parent or child table?

    • Max_Kondor
    • 4 yrs ago
    • Reported - view

    The formula is in another table (invoices). The fields are in a child table. I think these tables are only linked with a view not a reference. 

    • Sean
    • 4 yrs ago
    • Reported - view

    This formula works...

     

    let t := this;
    sum((select 'YourChildTable' where 'Invoice #' = t.'Invoice #' and not number(Choice) = 3).Amount)

    • Max_Kondor
    • 4 yrs ago
    • Reported - view

    this works great !!! Thank you !!

    • tunicoafx
    • 2 yrs ago
    • Reported - view

    Hei Sean I'm having a smilar problem. I'm trying to calculate the same situation but the formula is on the parent table.

    I'm making some mistake. what is the " 'invoice #' in the formula?

    • Sean
    • 2 yrs ago
    • Reported - view

    Hi Antonio,

     

    If you have a Reference field you can use a simpiler formula that is similar to what Alain Fontaine came up with in another thread, https://ninox.com/en/forum/technical-help-5ab8fe445fe2b42b7dd39ee7/counting-the-number-of-60787a4b1abc965bd1b6fa05?post=6079b8651abc965bd1b6fa59&page=1 

     

    sum(YourReferenceField[Choice != 3].Amount)

     

    'Invoice #' is the common field between the 2 unlinked tables. If you have a Reference field the connection is already made.

Content aside

  • 2 yrs agoLast active
  • 6Replies
  • 1854Views