Totaling Data

[boydr]

Good morning,

I have 2 columns of data. Column A has a date, and column 2 has a truck/trailer number. I want the total # of trucks, but if there is a duplicate date AND truck/trailer, then count it as one.

1/2/02 333344
1/2/02 333344 (this would be a count of 1)

Can anyone tell me how to do this?

Thank you in advance.

[PaddyD]

A horribly inelegant way to do it could be to concatenate the 2 colums & the count the unique results.
Not pretty, just simple...

Paddy

[eliW]

On 2002-05-14 04:18, boydr wrote:
Good morning,

I have 2 columns of data. Column A has a date, and column 2 has a truck/trailer number. I want the total # of trucks, but if there is a duplicate date AND truck/trailer, then count it as one.

1/2/02 333344
1/2/02 333344 (this would be a count of 1)

Can anyone tell me how to do this?

Thank you in advance.

Hi boydr,
Here is a simple way to do it using Advance Filter.
1) From Toolbar select Data | Filter | Advance Filter.
2) In "List Range" enter your 2 column range including titles.
3) Leave the "Criteria Range" field empty.
4)Tick on check box "Unique Records Only" and press OK.
Data will be filtered on unique records.
5) Now subtotal column B with the formula:
=SUBTOTAL(2,[your B column range])
Tell me if you succeeded.
Eli


[ This Message was edited by: eliW on 2002-05-14 09:03 ]

[boydr]

DADDYP,

How would I total on unique items only?

[Aladin Akyurek]

On 2002-05-14 15:25, boydr wrote:
DADDYP,

How would I total on unique items only?

=SUBTOTAL(2,...)

where 2 means COUNT.

Did you try Eli's proposal?

[PaddyD]

To the best of my knowledge I'm not a daddy yet...

Simple way to generate the subtotal:
Highlight the combined column (make sure it's got a column heading), select Data...Subtotals. select 'Count' from the 'Use fuctions' drop down. Collapse the groups to see just the totals.

Paddy

[Yogi Anand]

On 2002-05-14 04:18, boydr wrote:
Good morning,

I have 2 columns of data. Column A has a date, and column 2 has a truck/trailer number. I want the total # of trucks, but if there is a duplicate date AND truck/trailer, then count it as one.

1/2/02 333344
1/2/02 333344 (this would be a count of 1)

Can anyone tell me how to do this?

Thank you in advance.

You can do this using the DCOUNT function. Let us say the following data resides in A2:A7

Date TruckNo
1/2/02 333344
1/2/02 333343
1/2/02 333344
1/1/02 333345
1/1/02 333344
then let us put the following criterion, say in cell D2

=NOT(AND(A3=A4,B3=B4))
Now using the following formula

=DCOUNT(A2:B7,1,D1:D2) will give me the unique count of the Date_TruckNo combinations

Please post back if it works for you ... otherwise explain a little further and let us take it from there!

[Aladin Akyurek]

Yogi,

I don't think that DCOUNT will work. Try it on:

{"Date","TruckNo";37257,333345;37257,333344;37258,333344;37258,333343;37258,333344;37258,333344;37258,333343}

Aladin

[Yogi Anand]

On 2002-05-14 16:36, Aladin Akyurek wrote:
Yogi,

I don't think that DCOUNT will work. Try it on:

{"Date","TruckNo";37257,333345;37257,333344;37258,333344;37258,333343;37258,333344;37258,333344;37258,333343}

Aladin

Hi Aladin:
Works for me -- if I copied your data correctly, using my DCOUNT function, I got 4.

What did you get using my DCOUNT function -- and if you got an error or if you got another message, please post!

Regards

[boydr]

Thanks for getting back to me. I'll give it a try tomorrow at work, and let you know.