Solved

Excel 07: SUMIF Question

Posted on 2013-01-21
5
392 Views
Last Modified: 2013-01-21
Hi, I am working on a little excel program and have to use the sumif statement.  I am not great with this function, but will provide background information.   I am trying to track leaves of absences at work and for particular employees, they get a certain number of hours per year.  The problem is, the time does not reset at year end, rather it is a 12 month rolling.  So, the manager would enter the starting balance, but I need to calculate the current balance using a 12 months rolling point of view.   To get the check date to see if the hours should count or not, I entered the current date in column Y1 by using the function  =today()   Then below it in Y4, I did a simple =Y1-365.   So essentially, my Y4 is the cutoff date for being greater than or less than a year old

Ideally, I want the sumif statement to take the current balance (G12) and subtract the sum of hours from column C, but only when the date from column B is less than a year old compared to the current date. This is the code I've tried to use and it doesn't work.

=G12-(SUMIF(B18:B70),>Y4,C18:C70)

where:

G12 = starting balance
B18:B70 = Absence dates
C18:C70 =  Absence hours


                                    
      Starting Balance:                        132.4      
      Current Balance:                        124.4      
                                    
                                    
                                    
Date of Absence      Hours Used      comments/Notes                  
1/1/2012                    0                        
2/1/2012            8
0
Comment
Question by:akatz66
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 24

Expert Comment

by:Steve
ID: 38801791
You likely need to use SUMIFS

Can you provide a sample workbook with dummy data to give a more tailored answer.
0
 
LVL 13

Accepted Solution

by:
Shanan212 earned 250 total points
ID: 38801809
=G12-SUMIF(($B$18:$B$70),">"&(TODAY()-365),$C$18:$C$70)

Open in new window

0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 250 total points
ID: 38801880
You can use Y4 in SUMIF like this

=G12-SUMIF(B18:B70,">"&Y4,C18:C70)

regards, barry
0
 

Author Comment

by:akatz66
ID: 38802075
I've requested that this question be deleted for the following reason:

I was able to figure out the code myself. Thanks!
0
 
LVL 13

Expert Comment

by:Shanan212
ID: 38802076
Could you please post the solution here as well? Thanks!
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question