Subtotals

[Stan]

I have 2 columns of data that I need subtotals on both.

Group Start Time
7750 8:00 am
7750 8:00 am
7750 8:30 am
7750 8:30 am
7750 8:30 am
7751 7:30 am
7751 7:30 am
7751 8:30 am

In the above example I would like the outcome to be:

Group Start Time Count
7750 8:00 am 2
7750 8:30 am 3
7751 7:30 am 2
7751 8:30 am 1

Of course I can subtotal either column but I'm not sure how I can subtotal
them together to achieve the desired outcome. My goal is to run a macro that
would do this for me all at once.

Any help you can provide would be much appreciated!

Many thanks!

 

[Duke Carey]

First step is to highlight the range of data, then use Data->Filter->Advanced
filter.
Choose to Copy to a new location, and check Unique records only.

Now assuming your two original columns are in A & B, and the unique,
filtered data is in colmns D & E, both starting in row 2, use this formula in
F2 and copy it down

=SUMPRODUCT(--($A$2:$A$92=D2),--($B$2:$B$92=E2))

adjust the ranges to reflect your own data

 

[JLatham]

As much as I like writing code, I don't think you need it here. Assuming
your data starts at row 2, and is in columns A and B, a formula such as this
somewhere on row 2, perhaps in column C? will show you the count for a
Group/Start Time combination when it first appears in the series:
=IF(OR(A2<>A1,B2<>B1),SUMPRODUCT(--(A$2:A$65536=A2),--(B$2:B$65536=B2)),"")
Put that formula in C2 (or as I said, any column on row 2 other than A or B)
and then fill it down as far as your data entries go. You can fill it on
down the sheet later as more entries are added also.

A variation of this would be to be able to enter a group number in a cell
(as D2) and a start time in another (as E2) and then put this formula in F2
=SUMPRODUCT(--(A$2:A$65536=D2),--(B$2:B$65536=E2))
and as you change entries in D2 and E2, the result will change to show you
the subtotal for that pairing in columns A and B.

Hope this helps you find a solution.