[Webinar] Streamline your web hosting managementRegister Today

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 408

# 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
2 Solutions

Commented:
You likely need to use SUMIFS

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

Commented:
``````=G12-SUMIF((\$B\$18:\$B\$70),">"&(TODAY()-365),\$C\$18:\$C\$70)
``````
0

Commented:
You can use Y4 in SUMIF like this

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

regards, barry
0

Author Commented:
I've requested that this question be deleted for the following reason:

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

Commented:
Could you please post the solution here as well? Thanks!
0

## Featured Post

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