Formula to sum where a field could be one of 2 values 1

[Deb100]

I am building a high level report, showing total figures per client for all stocks. I need help to show the total of profits on disposals.

My Excel spreadsheet is as follows:

Client Details Stock Net Charges
Smith Purchase AAA 10000.00
Smith Sold AAA -15000.00
Smith Purchase BBB 2000.00
Smith Sold BBB -2500.00
Smith Purchase CCC 1800.00

I need a formula to calculate the sum of 'Net Charges' (for 'Purchase' and 'Sold') where a Stock has been 'Sold', ie. the formula would return -5500.00 in this example.

 

[lbass]

If you insert a group on stock, you can go to report->selection formula->GROUP and enter:

maximum({table.details},{table.stock}) = "Sold"

This will display all records for only those stocks that have been sold. Then you can insert a running total that is the sum of net charges, evaluate for each record, reset on change of client. Place the running total in the client group footer. If you want a report grand total, insert a second running total like the first, but with reset = "never".

-LB

 

[Deb100]

Hi LB

Thanks for that, however, would the insertion of the group mean that only stocks that have been sold will be displayed for the whole report, as this would not work for other totals that I need to display?

 

[lbass]

Yes, this would remove stocks from the display that were not sold, although they would still contribute to inserted summaries. In other words, if you were using inserted summaries instead of running totals, the non-group selected stocks would contribute to those summaries. If you still need them to actually display, then I think you should remove the group selection and use the same running totals, but instead of evaluating on each record, choose "use a formula" and enter:

maximum({table.details},{table.stock}) = "Sold"

This still requires a group on stock, although both group header and group footer could be suppressed.

-LB