Forum Discussion

SarahDriscoll1's avatar
SarahDriscoll1
Qrew Trainee
2 years ago

Total of a Multiple Choice Column across Multiple Fields

Hello,

I have a report where each record is a building. The information is broken down to show where region the building is in (there are four regions) and then show the risk of four different areas within a building and assigning a risk level.Users can rate those areas as high, medium, low or complete.

I need to show within a summary report for each region how many of these are high, medium, low or completed. I have tried stacking an If() formula but it currently will override later statements and not produce the correctly totals. I have a formula that looks and summarizes for each record how many have matching values:

Sum(
If([Area1 Risk]="High",1,0),
If([Area 2 Risk]="High",1,0),
If([Area 3 Risk]="High",1,0),
If([Area 4 Risk]="High",1,0))

Is there a way to use this information to help summarize and show the correct totals? Here is what each value should be totaling:

Completed: 94

Low: 66

Medium: 75

High: 49

Total: 284



------------------------------
Sarah Driscoll
------------------------------
  • Assuming I understand correctly: Is your row grouping currently set to the Risk field, and your column grouping set to Region?  Lastly, does the Risk field include the value complete?  If the Risk field does NOT contain the completed value, I would create a new text formula field that displays the Risk field value, unless the status is completed.  If the status is completed, output "Completed".  This will give you both values in one field.



    ------------------------------
    John Ross
    ------------------------------