Formula to add fields if a record number matches

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

I need to add the total $ of multiple records, but only if the ID #ers match.    I think I am making this harder than it should be, but I am stumped.  Essentially this is a summary field, but within the table, not part of a relationship.

PID is a numeric field, Total TCV is a currency field

If [PID #] is the same, add the [Total TCV]

 

Photo of Nikki

Nikki

  • 284 Points 250 badge 2x thumb

Posted 4 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
The only way to do this is with a Summary report.  There is no equivalent to =SUMIF(  )  like Excel has.
Photo of Nikki

Nikki

  • 284 Points 250 badge 2x thumb
At least I was stumped for a reason.   It there a way to summarize in another table without it being a key field?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
You would need to make a Summary Report of the unique PID#'s.  Then use the More button to copy those unique Summary Records to another table.  Then set the Key field to be the PID#.  Ten make the relationships where 1 PID# has many detail records.  Then you can do any summary totals you like.

BUT!,  you will need to refresh the master list from time to time as new PID's are added to the details table.  But you can still copy across the whole summary report and it will  add missing PID records where it needs to and leave the existing records alone.
Photo of Nikki

Nikki

  • 284 Points 250 badge 2x thumb
Thanks for your help.  I'll see if this will work for my needs.