Need help with a formula - Distinct Count?

[Former Member]

Hello,

I need help with the following scenario. I need to know how many ship ID's for an item number there are in the same group. Look at Item# 105, it shipped twice on the same order with two different ShipID's, therefore the No of Ship ID's should be two. I'm pulling my hair trying to build this formula and I'm failing all the time.

Any help would be greatly appreciated.

Group:  Order#1

ItemNumber       Qty      ShipID   No of Ship ID's

105                     5          1000          2

205                    10         1000          1

105                     7          1005          2

[abhilash_kumar · Accepted solution]

Hi Jack,

1) Insert a second group on the ItemNumber field

2) Go to Insert Summary > Choose the 'Ship ID' field as the Field to Summarize > Choose 'Count' as the Summary Operation

3) Under 'Summary Location', chose Group #2 (ItemNumber)

4) Suppress the Group #2 header and footer.

5) Move the summary to the details section

-Abhilash

[Former Member · Accepted solution]

It's not allowing me to move it to the details. I can move the summary all over the groups, but not the details.

[abhilash_kumar · Accepted solution]

Jack,

Create a formula with this code:

Count({Ship_ID}, {Group_field#2})

Place this formula field on the details.

-Abhilash

[Former Member · Accepted solution]

That's it! Thank you!