Total numeric values for any given date

[lazzareo]

Hi,

I have a basic spreadsheet with 4 columns of data:

• Column A contains a date
• Column D contains a numeric value

I want to run a macro/query that outputs the total numeric values for any given date.

e.g.

Week Commencing	Name	Location	Days
02 January 2017	anonymous	London	4
02 January 2017	anonymous	London	4
02 January 2017	anonymous	London	2
02 January 2017	anonymous	London	4
09 January 2017	anonymous	London	3
09 January 2017	anonymous	London	4
09 January 2017	anonymous	London	2
09 January 2017	anonymous	London	2
16 January 2017	anonymous	London	5
16 January 2017	anonymous	London	2
16 January 2017	anonymous	London	5
16 January 2017	anonymous	London	4

<colgroup><col><col span="2"><col></colgroup><tbody>
</tbody>

How could I do this?

Thanks.

 

[FormR]

Hi, maybe you could consider a formula:

Excel 2013/2016
	A	B	C	D	E	F	G
1	Week Commencing	Name	Location	Days		Given Day	Total
2	02 January 2017	anonymous	London	4		09 January 2017	11
3	02 January 2017	anonymous	London	4
4	02 January 2017	anonymous	London	2
5	02 January 2017	anonymous	London	4
6	09 January 2017	anonymous	London	3
7	09 January 2017	anonymous	London	4
8	09 January 2017	anonymous	London	2
9	09 January 2017	anonymous	London	2
10	16 January 2017	anonymous	London	5
11	16 January 2017	anonymous	London	2
12	16 January 2017	anonymous	London	5
13	16 January 2017	anonymous	London	4
ALLCOMPS

Cell Formulas
Range	Formula
G2	=SUMIFS(D:D,A:A,F2)

 

[MickG]

Try this for results starting "G1":-

[COLOR="Navy"]Sub[/COLOR] MG20Nov31
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Dt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Dt = CDbl(DateValue(Dn.Value))
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dt) [COLOR="Navy"]Then[/COLOR]
        .Add Dt, Dn.Offset(, 3).Value
    [COLOR="Navy"]Else[/COLOR]
        .Item(Dt) = .Item(Dt) + Dn.Offset(, 3)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
Range("G1").Resize(.Count, 2).Value = Application.Transpose(Array(.Keys, .items))
Range("G1").Resize(.Count).NumberFormat = "dd-mmm-yy"
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[lazzareo]

Thanks FormR & MickG!!! Much appreciated

 

[MickG]

You're welcome