Sorting large amount of hourly data into monthly data

[Saidsuch]

Dear Friends,

I have a large amount of data as follows. I want to sort them into another column as follows. The sum of each mother should appear together with the respective day and month. Could you kindly help me on this pls

Kind regards

Said


Month Day Value
4 8 207
4 8 180
4 8 152
4 8 162
4 8 171
4 8 219
4 8 86
4 8 63
4 8 61
4 9 64
4 9 64
4 9 69
4 9 80
4 9 71
4 9 73
4 9 69
4 9 69
4 9 64
4 9 69
4 9 70
4 9 69
4 9 56
4 9 52
4 9 54
4 9 50
4 9 57
4 9 71
4 9 118
4 9 128
4 9 130
4 9 138
4 9 142
4 9 115
4 10 112
summary
4 8 1301
4 9 1942

[63falcondude]

Hello and welcome to the forum.

With 4 and 8 in E2 and F2 respectively, try this in G2:

=SUMIFS(C:C,A:A,E2,B:B,F2)

Drag the formula down.

[63falcondude]

If you have Excel 2003, as your profile suggests, try this in G2:

=SUMPRODUCT((A$2:A$35=E2)*(B$2:B$35=F2)*(C$2:C$35))

Excel 2003 does not have the SUMIFS function.

[Saidsuch]

working.xlsxworking.xlsx

Hi all

Thank you very much for your response but it does not list day and month as appear in the attached Excel sheet. All values sum up to o.

My requirement is I need to get a summary as when the formula is dragged down.

summary
4 8 1301
4 9 1942

Kind regards

Said

[FlameRetired]

In your upload try this.

(By the way, you've uploaded and *.xlsx file. Your profile shows Excel 2003 as noted. Please update your profile to reflect the version(s) you use. It's important. Members tailor responses with those kind of things in mind. Thanks.)

	A	B	C	D	E	F	G	H
1	Month	Day	Value
2	4	8	207		4	8	1301	In G2: =SUMIFS(C:C,A:A,E2,B:B,F2)
3	4	8	180		4	9	1942
4	4	8	152		4	10	113
5	4	8	162
6	4	8	171
7	4	8	219
8	4	8	86
9	4	8	63
10	4	8	61
11	4	9	64
12	4	9	64
13	4	9	69
14	4	9	80
15	4	9	71
16	4	9	73
17	4	9	69
18	4	9	69
19	4	9	64
20	4	9	69
21	4	9	70
22	4	9	69
23	4	9	56
24	4	9	52
25	4	9	54
26	4	9	50
27	4	9	57
28	4	9	71
29	4	9	118
30	4	9	128
31	4	9	130
32	4	9	138
33	4	9	142
34	4	9	115
35	4	10	113

[Saidsuch]

Thank you all these. You are real gurus. All three members must be thanked

[Saidsuch]

Thanks but I am not allowed to add any reputation by the system. It says 10 reached

[63falcondude]

Glad we could help. Thanks for the rep!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[Saidsuch]

Dear Friends,

One more additional help before closing this, is it possible for me to get the average of that particular (to the column H) as totals have been generated under the same conditions. I tried it myself several times but failed.

Thanks

Said

[JohnTopley]

Change SUMIFS to AVERAGEIFS ???

[Saidsuch]

Thanks for your quick response but It says you have entered too few arguments for this function

[JohnTopley]

What did you enter ???


Changing to this ..

=AVERAGEIFS(C:C,A:A,E2,B:B,F2)

from this

=SUMIFS(C:C,A:A,E2,B:B,F2)

works OK.

[Saidsuch]

Thank you so much

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.