CR 8.5 - Subtotal of column by person

[ambra19]

Column 1 Column 2
Bob J
Bob J
Bob N
Bob S
Subtotal = 3
Chuck J
Chuck N
Chuck S
Subtotal = 2

I need the formula to get the subtotal of J's and S's for each person.

 

[synapsevampire]

Create a Running Total count of the field and in the evaluate->Use a Formula place:

{table.column2} in ["J", "S"]

-k

 

[ambra19]

Sorry, we don't need a running total just a total per person. Total # of J's and S's per person

Here is the formula I currently have:

numbervar totalsales;

if {Column2} = "J" or {Column2} = "S" then
totalsales := totalsales + 1
else totalsales := totalsales + 0

Do I need a loop? And if so, how?

 

[bdreed35]

I am assuming by your layout sample that you are already grouping by Name.

Create 1 formula to determine if the value is a J and another formula to determine if the value is an S.

{@JCount}

if {table.field} = "J" then
    1
else
    0

************************************
{@SCount}

if {table.field} = "S" then
    1
else
    0

************************************
Drag the 2 formulas to the detail section.
Right click each, Format Field, and click Suppress under the Common tab.

Now create a summary for each by Right clicking each formula in the detail section, choose insert, summary.
Make sure that you choose Sum, and it should be at the Name group level, not overall.


~Brian

 

[ambra19]

It works, thanks Brian!

 

[synapsevampire]

A running total can be by person, and it's less code.

Just select reset on person.

Or you can use the above formulas.

Brian's code doesn't match with your example, it shows the counts combined, not separate.

-k