Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 406
  • Last Modified:

Excel 07: SUMIF Question

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
akatz66
Asked:
akatz66
2 Solutions
 
SteveCommented:
You likely need to use SUMIFS

Can you provide a sample workbook with dummy data to give a more tailored answer.
0
 
Shanan212Commented:
=G12-SUMIF(($B$18:$B$70),">"&(TODAY()-365),$C$18:$C$70)

Open in new window

0
 
barry houdiniCommented:
You can use Y4 in SUMIF like this

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

regards, barry
0
 
akatz66Author Commented:
I've requested that this question be deleted for the following reason:

I was able to figure out the code myself. Thanks!
0
 
Shanan212Commented:
Could you please post the solution here as well? Thanks!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now