Subtotalling multiple data groups

[redjay]

Hi Guys<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Wonder if any of you are able to help with this one. I have attached a JPG of what I have in mind.<o></o>
<o></o>
I have a simple sales worksheet showing Customer Name in Column A, Date of sale in Column B and Quantity in Column C. The data is sorted in that order.<o></o>
<o> </o>
I want some way of subtotalling the quantities by Name and Date. In other words, I want to be able to see how much each customer ordered on each date and for that value to be returned in Column D. Hopefully the attached image will make it clearer.

<o></o>
<o> </o>
I don’t think the Excel subtotals function would work for this and I don’t wish to use filters or pivot tables as I need all the data visible at the same time. Ideally I would like some code or a formula (or indeed an Excel function im not aware of!).<o></o>
<o> </o>
Many thanks <o></o>

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG09May21
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] tot [COLOR="Navy"]As[/COLOR] Double
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & rows.Count).End(xlUp))
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        tot = tot + Dn.Offset(, 2)
            [COLOR="Navy"]If[/COLOR] Not Dn & Dn(, 2) = Dn(2) & Dn(2, 2) [COLOR="Navy"]Then[/COLOR]
                Dn(, 4) = tot
                tot = 0
            [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[redjay]

Thanks Mick

However, Im getting a Run time error 13, Type Mismatch error ?

 

[MickG]

My fault, change the Line below, the code is trying to read you headers as a number !!!

Set Rng = Range(Range("A2"), Range("A" & rows.Count).End(xlUp))

Regards Mick

 

[redjay]

You are a genius, sir

Much appreciated, works perfectly!

 

[Akashwani]

hi,

A formula solution that might work in D2 and copied down...

=IF(B2=B3,"",SUMPRODUCT(--($A$2:$A$23=A2),--($B$2:$B$23=B2),$C$2:$C$23))

Excel Workbook
	A	B	C	D
1	Name	Date	Quantity	Total
2	Jones	01/01/2010	2	*
3	Jones	01/01/2010	2	*
4	Jones	01/01/2010	2	6
5	Jones	01/08/2011	3	*
6	Jones	01/08/2011	3	6
7	Smith	01/01/2010	2	*
8	Smith	01/01/2010	2	*
9	Smith	01/01/2010	2	6
10	Smith	13/08/2010	3	*
11	Smith	13/08/2010	3	*
12	Smith	13/08/2010	3	*
13	Smith	13/08/2010	3	*
14	Smith	13/08/2010	3	15
15	Andrew	14/05/2010	2	*
16	Andrew	14/05/2010	2	*
17	Andrew	14/05/2010	2	6
18	Andrew	03/06/2010	3	*
19	Andrew	03/06/2010	3	*
20	Andrew	03/06/2010	3	9
21	Andrew	05/05/2011	4	*
22	Andrew	05/05/2011	4	*
23	Andrew	05/05/2011	4	12
Sheet4

Oops! Too late.

Ak

 

[redjay]

Thanks Akashwani- I might need that too

Thank you both for your efforts..

 

[jrabi]

Be careful with the sumproduct formula, it only works if column a is sorted, if names are not sorted this formula works incorrectly.

The Macro will work perfect.

I would not use this formula, you could have spreadsheet sorted by date, then the formula does not work.

 

[Akashwani]

Hi jrabi,

Does this seem better?

Excel Workbook
	A	B	C	D	E
1	Name	Date	Quantity	*	Total
2	Jones	01/01/2010	2	*	*
3	Jones	01/01/2010	2	*	*
4	Jones	01/01/2010	2	*	6
5	Jones	13/08/2010	3	*	*
6	Jones	13/08/2010	3	*	*
7	Smith	13/08/2010	3	*	*
8	Smith	13/08/2010	3	*	*
9	Smith	13/08/2010	3	*	9
10	Smith	01/01/2010	2	*	*
11	Smith	01/01/2010	2	*	*
12	Smith	01/01/2010	2	*	6
13	Smith	01/08/2011	3	*	*
14	Smith	01/08/2011	3	*	6
15	Andrew	14/05/2010	2	*	*
16	Andrew	14/05/2010	2	*	*
17	Andrew	14/05/2010	2	*	6
18	Andrew	03/06/2010	3	*	*
19	Andrew	03/06/2010	3	*	*
20	Andrew	03/06/2010	3	*	9
21	Andrew	05/05/2011	4	*	*
22	Andrew	05/05/2011	4	*	*
23	Andrew	05/05/2011	4	*	12
Sheet1

Or this....

Excel Workbook
	A	B	C	D	E
1	Name	Date	Quantity		Total
2	Jones	01/01/2010	2
3	Jones	01/01/2010	2
4	Jones	01/01/2010	2
5	Smith	01/01/2010	2
6	Smith	01/01/2010	2
7	Smith	01/01/2010	2		6
8	Andrew	14/05/2010	2
9	Andrew	14/05/2010	2
10	Andrew	14/05/2010	2		6
11	Andrew	03/06/2010	3
12	Andrew	03/06/2010	3
13	Andrew	03/06/2010	3		9
14	Jones	13/08/2010	3
15	Jones	13/08/2010	3
16	Smith	13/08/2010	3
17	Smith	13/08/2010	3
18	Smith	13/08/2010	3		9
19	Andrew	05/05/2011	4
20	Andrew	05/05/2011	4
21	Andrew	05/05/2011	4		12
22	Smith	01/08/2011	3
23	Smith	01/08/2011	3		6
Sheet1

Ak

 

[jrabi]

Not sure if it seems better, but your formula is flawed, I would not use...

This solution would require an array formula to look at both columns...