Formula to Add critiera - SUMIFS?

hello,

I am trying to write a formula which adds a total number of hours for a particular person for each month -ie. a monthly total.

I have been playing with the use of a SUMIFS forumlas so I can use multiple criteria but I have not been able to write it with much luck!..

Is SUMIFS the best way to approach this requirements?

I hope the spreadsheet makes sense ;)
Hours-Total-Sheet---Example.xlsx
gisvpnAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
LazyFolkCommented:
try this :

=SUMIF(B1:B143;"=Jim";C1:C143)

replace "=Jim" with "=Amy" for Amy's hours
0
 
Peter KwanAnalyst ProgrammerCommented:
You may use SUMPRODUCT.

For example:

Total for Nov for Jim = SUMPRODUCT((A:A>=DATEVALUE("1/11/2012"))*(A:A<=DATEVALUE("30/11/2012"))*(B:B=F6),C:C )
0
 
helpfinderIT ConsultantCommented:
isn´t it Pivot table suitable for you enough?
Just use pivot table filter to show months (I added one column to your table with month number)
Copy-of-Hours-Total-Sheet---Exam.xlsx
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
helpfinderIT ConsultantCommented:
or maybe this is how you wanted to have it with SUMIFS (one date is missing in your table so SUM of C rows is not the same as SUM of data after SUMIFS)
Copy-of-Hours-Total-Sheet---Exam.xlsx
0
 
gisvpnAuthor Commented:
thank you all for your posts.

pkwan
Do you know If I can use within SUMPRODUCT the DateValue function with the date being referenced in another cell, for example :

DATEVALUE(J5)

I cannot seem to get it to work this way if I were to reference the start and end dates from another cell, I was just trying to think of another way to speed up writting the cells ;)


helpfinder
thank you for your posts also.
0
 
Peter KwanAnalyst ProgrammerCommented:
Hi, gisvpn,

Is there any example that you tried not working?
0
 
gisvpnAuthor Commented:
sure please see attached
Hours-Total-Sheet---Example---wi.xlsx
0
 
Peter KwanAnalyst ProgrammerCommented:
You don't need datevalue in this case.  DATEVALUE function is for converting a text value to a date value. Since your G4, G5 are already date value, there is no need to use this function.

Just  the following should work.

= SUMPRODUCT((A:A>=G4)*(A:A<=G5)*(B:B=F6),C:C )
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
barry houdiniCommented:
.....SUMIFS would probably be preferable though - it'll certainly be more efficient if you are referencing whole columns. The SUMIFS version would look like this for G6 copied down

=SUMIFS(C:C,A:A,">="&G$4,A:A,"<="&G$5,B:B,$F6)

regards, barry
0
 
gisvpnAuthor Commented:
Hi barryhoudini,

You're right, it makes a HUGE difference! thank you for the revision ;) It runs in a fraction of the time now ;)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.