Calculate column in report but only the rows that the value in another column equal value "x"

[whamilton]

I'm trying to make a calculated text box in a Report. I need it to only sum the values in [Total Value] if the value in [Stages]="1"



I haven't been able to come up with the correct syntax.

[Micron]

Usually easier to create a calculated field in the query behind the report, then bind the report control to it. However, the report structure is not clear. [Total Value] and [Stages] are fields in the detail section (they repeat for each record)? Or is this calculation being done over a group? A pic of the report design showing these controls might help.

[whamilton]

I've attached a screenshot of the report design. My intent is to have several total fields at the top of the report. A sum Total Value for the entire report and then a sum for each of the Stages.Opportunities Report.pdf

[whamilton]

I've attached a screenshot of the report design. My intent is to have several total fields at the top of the report. A sum Total Value for the entire report and then a sum for each of the Stages.Opportunities Report.pdf

[Gicu]

You could try:
Total Value Stage 1: =Sum(IIf([Stages]='1',1,0)
Total Value Stage 2: =Sum(IIf([Stages]='2',1,0)
etc.
Cheers,

[Bulzie]

Or maybe: DSum("Total Value", "Your Table", "Stages = 1")

[CJ_London]

You could use this formula in the controlsource for a textbox in the report header or footer

=sum(-totalvalue*(stages=“1”))

Only include the brackets around 1 if the stages field is text, numbers do not use identifiers

For the future, tell us what you have tried and why it didn’t work - error message, wrong result , something else