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>

 

[Akashwani]

Hi jrabi,

After re-reading the original post, and using the data provided this seems to work...

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	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
Sheet1

But, I could be wrong, again
If you think this formula is flawed, would you care to provide one so that I can see where I am going wrong?

Ak

 

[jrabi]

Yes an IF(And will work since it views col a and b and not just B as your original, one does not need the absolutes(see below) which makes it easier to migrate from WS to WS:

=IF(AND(A2=A3,B2=B3),"",SUMPRODUCT(--(A:A=A2),--(B:B=B2),C:C))

 

[Akashwani]

Hi jrabi,

Thanks very much for putting me on the right track, much appreciated.
I'll save that formula for future reference.

Ak