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 :)
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,563
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Bryan,

Welcome to MrExcel.

If I am understanding correctly then SUMIFS should do it.

Eg...

Excel 2007
BCDEFGHIJ
1ProcessActivityHrsABC
2AMeet1Meet1.553
3BMeet2Read Newspaper200
4ALunch2Crafty Snooze0.2500
5BMeet1Lunch200
6CMeet3
7AMeet0.5
8ACrafty Snooze0.25
9BMeet1
10ARead Newspaper2
11BMeet1
Sheet1
Cell Formulas
RangeFormula
H2=SUMIFS($F$2:$F$20,$B$2:$B$20,H$1,$C$2:$C$20,$G2)


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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,627
Messages
5,838,444
Members
430,548
Latest member
hh_dh2001

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top