SumIF question, multiple criteria

Rav_Singh

New Member
Joined
Jun 29, 2019
Messages
28
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;

QtrlyModified DateDocsEmails
Q1-201815/01/201810
Q1-201816/01/201810
Q2-201817/04/201803
Q2-201818/05/201800
Q3-201819/07/201810
Q3-201822/08/201801
Q4-201823/10/201802
Q4-201825/12/201803

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

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.

QtrlyDocsEmails
Q1-201820
Q2-201803
Q3-201811
Q4-201805

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

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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,513
Office Version
  1. 365
Platform
  1. Windows
How about
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;;">Qtrly</td><td style="font-weight: bold;;">Modified Date</td><td style="font-weight: bold;;">Docs</td><td style="font-weight: bold;;">Emails</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;;">Qtrly</td><td style="font-weight: bold;;">Docs</td><td style="font-weight: bold;;">Emails</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Q1-2018</td><td style="text-align: right;;">15/01/2018</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Q1-2018</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Q1-2018</td><td style="text-align: right;;">16/01/2018</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Q2-2018</td><td style="text-align: right;;">0</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Q2-2018</td><td style="text-align: right;;">17/04/2018</td><td style="text-align: right;;">0</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Q3-2018</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Q2-2018</td><td style="text-align: right;;">18/05/2018</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Q4-2018</td><td style="text-align: right;;">0</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Q3-2018</td><td style="text-align: right;;">19/07/2018</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Q3-2018</td><td style="text-align: right;;">22/08/2018</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Q4-2018</td><td style="text-align: right;;">23/10/2018</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Q4-2018</td><td style="text-align: right;;">25/12/2018</td><td style="text-align: right;;">0</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Summary</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H2</th><td style="text-align:left">=SUMIF(<font color="Blue">$A:$A,$G2,C:C</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I2</th><td style="text-align:left">=SUMIF(<font color="Blue">$A:$A,$G2,D:D</font>)</td></tr></tbody></table></td></tr></table><br />
 

Rav_Singh

New Member
Joined
Jun 29, 2019
Messages
28
This is exactly what I needed! Thanks for making me feel silly! :)

Much appreciated as always.

How about
ABCDEFGHI
1QtrlyModified DateDocsEmailsQtrlyDocsEmails
2Q1-201815/01/201810Q1-201820
3Q1-201816/01/201810Q2-201803
4Q2-201817/04/201803Q3-201811
5Q2-201818/05/201800Q4-201805
6Q3-201819/07/201810
7Q3-201822/08/201801
8Q4-201823/10/201802
9Q4-201825/12/201803

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Summary

Worksheet Formulas
CellFormula
H2=SUMIF($A:$A,$G2,C:C)
I2=SUMIF($A:$A,$G2,D:D)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,513
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,114,614
Messages
5,549,012
Members
410,889
Latest member
laingwb
Top