SumIF question, multiple criteria

[Rav_Singh]

I am attempting to summarise a large data-set and am having trouble trying to collate the sum total.

Hopefully the below example displays correctly on this page.

Sample of data-set is below;

[TABLE="width: 323"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Qtrly[/TD]
[TD]Modified Date[/TD]
[TD]Docs[/TD]
[TD]Emails[/TD]
[/TR]
[TR]
[TD]Q1-2018[/TD]
[TD]15/01/2018[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Q1-2018[/TD]
[TD]16/01/2018[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Q2-2018[/TD]
[TD]17/04/2018[/TD]
[TD]0[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Q2-2018[/TD]
[TD]18/05/2018[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Q3-2018[/TD]
[TD]19/07/2018[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Q3-2018[/TD]
[TD]22/08/2018[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Q4-2018[/TD]
[TD]23/10/2018[/TD]
[TD]0[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Q4-2018[/TD]
[TD]25/12/2018[/TD]
[TD]0[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

I would like to calculate the sum totals of 'Docs' and 'Emails' based upon the Qtrly dates.

i.e. so for the answers it should hopefully reflect the table below.

[TABLE="width: 262"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]Qtrly[/TD]
[TD]Docs[/TD]
[TD]Emails[/TD]
[/TR]
[TR]
[TD]Q1-2018[/TD]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Q2-2018[/TD]
[TD]0[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Q3-2018[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Q4-2018[/TD]
[TD]0[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

This seems a common formula however the different CountIF/SumIF formulas have not returned the values I was hoping for. Any help would be gratefully received.

Rav

 

[Fluff]

How about

Book1
	A	B	C	D	E	F	G	H	I
1	Qtrly	Modified Date	Docs	Emails			Qtrly	Docs	Emails
2	Q1-2018	15/01/2018	1	0			Q1-2018	2	0
3	Q1-2018	16/01/2018	1	0			Q2-2018	0	3
4	Q2-2018	17/04/2018	0	3			Q3-2018	1	1
5	Q2-2018	18/05/2018	0	0			Q4-2018	0	5
6	Q3-2018	19/07/2018	1	0
7	Q3-2018	22/08/2018	0	1
8	Q4-2018	23/10/2018	0	2
9	Q4-2018	25/12/2018	0	3
Summary

Cell Formulas
Range	Formula
H2	=SUMIF($A:$A,$G2,C:C)
I2	=SUMIF($A:$A,$G2,D:D)

 

[Rav_Singh]

This is exactly what I needed! Thanks for making me feel silly!

Much appreciated as always.

How about

	A	B	C	D	E	F	G	H	I
Qtrly	Modified Date	Docs	Emails	Qtrly	Docs	Emails
Q1-2018	Q1-2018
Q1-2018	Q2-2018
Q2-2018	Q3-2018
Q2-2018	Q4-2018
Q3-2018
Q3-2018
Q4-2018
Q4-2018

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]15/01/2018[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]16/01/2018[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]17/04/2018[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]18/05/2018[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]19/07/2018[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]22/08/2018[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]23/10/2018[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]25/12/2018[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Summary​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]=SUMIF($A:$A,$G2,C:C)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I2[/TH]
[TD="align: left"]=SUMIF($A:$A,$G2,D:D)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

You're welcome & thanks for the feedback