Having difficulty with SUMIF and SUMIFS

MrCrimmy

New Member
Joined
Sep 26, 2014
Messages
14
Hello everyone :)

I need some help with a database I'm trying to build in excel. Currently I have a list of all processes carried out in my office for my team. There are 4 main processes and each process has a subset of activities relating to it, for example process "BAU" has several activities such as meetings and training. I am calculating the man hours spent on each activity using the following SUMIF function (Each worksheet is divided into weeks):


=SUMIF('Sep 01'!$C$4:$C$120,C6,'Sep 01'!$F$4:$F$120)+SUMIF('Sep 08'!$C$4:$C$120,C6,'Sep 08'!$F$4:F$120)+SUMIF('Sep 15'!$C$4:$C$120,C6,'Sep 15'!$F$4:F$120)

c = activity column, F = man hours column

My problem is that some processes have the same activities, for example BAU may have meetings but the product maintenance process may also have a meetings activity. Is there anyway I can use a SUMIF or SUMIFS function to add up the man hours depening on both variables so that BAU meetings will be counted seperately from process maintenance meetings.

If anyone could help me out that would be greatly appreciated.

thanks

Bryan :)
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,059
Office Version
  1. 2013
Platform
  1. Windows
Bryan,

Welcome to MrExcel.

If I am understanding correctly then SUMIFS should do it.

Eg...
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></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><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Process</td><td style=";">Activity</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">Hrs</td><td style="text-align: right;;"></td><td style="text-align: center;;">A</td><td style="text-align: center;;">B</td><td style="text-align: center;;">C</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">A</td><td style=";">Meet</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">1</td><td style=";">Meet</td><td style="text-align: center;;">1.5</td><td style="text-align: center;;">5</td><td style="text-align: center;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">B</td><td style=";">Meet</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">2</td><td style=";">Read Newspaper</td><td style="text-align: center;;">2</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">A</td><td style=";">Lunch</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">2</td><td style=";">Crafty Snooze</td><td style="text-align: center;;">0.25</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">B</td><td style=";">Meet</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">1</td><td style=";">Lunch</td><td style="text-align: center;;">2</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">C</td><td style=";">Meet</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">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></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">A</td><td style=";">Meet</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">0.5</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: #161120;text-align: center;">8</td><td style=";">A</td><td style=";">Crafty Snooze</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">0.25</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: #161120;text-align: center;">9</td><td style=";">B</td><td style=";">Meet</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">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></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">A</td><td style=";">Read Newspaper</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">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></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">B</td><td style=";">Meet</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">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></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><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: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><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: #E0E0F0;color: #161120">H2</th><td style="text-align:left">=SUMIFS(<font color="Blue">$F$2:$F$20,$B$2:$B$20,H$1,$C$2:$C$20,$G2</font>)</td></tr></tbody></table></td></tr></table><br />

Hope that helps.
 
Last edited:

MrCrimmy

New Member
Joined
Sep 26, 2014
Messages
14
Thank you so very much for your help! I was actually quite close myself, just needed a boost!!

Have a great weekend!

Regards

Bryan
 

Watch MrExcel Video

Forum statistics

Threads
1,109,522
Messages
5,529,329
Members
409,863
Latest member
stacy09
Top